SQL Extract value of specific cells from GROUPed rows by condition of another column of the same row

Question

The initial table:

ROW   TEAM   BUDGET     DATE
 1      A      50    2021-01-17
 2      A      100   2021-01-24
 3      A      140   2021-01-31
 4      A      250   2021-02-07
 5      A      480   2021-03-08
 6      B      70    2021-03-15
 7      B      90    2021-03-16
 8      B      130   2021-04-08
 9      B      340   2021-05-17
 10     B      680   2021-06-26

I need:

1. To know how much budget was spent for each team (SUM)
2. Budget for the first time period per team
3. Budget for the last time period per team

The first thing I did - groped stats by team

ROW   TEAM   BUDGET_TOTAL 
 1      A        1020   
 2      B        1310

However, I don't know what to use in order to get values of the budget for the first and last period for each team and add them to the table so the result will be the following:

ROW   TEAM   BUDGET_TOTAL INITIAL_BUDJET LAST_MONTH_BUDGET
 1      A        1020            50             480 
 2      B        1310            70             680  

Answer 1

Try below approach

select team, 
  sum(monthly_budget) budget_total,
  array_agg(initial_monthly_budget order by month limit 1)[offset(0)] initial_budget,  
  array_agg(monthly_budget order by month desc limit 1)[offset(0)] last_month_budget  
from ( 
  select team, date_trunc(date, month) month,
    array_agg(budget order by date limit 1)[offset(0)] initial_monthly_budget,
    sum(budget) monthly_budget
  from `project.dataset.table`
  group by team, month
)
group by team    

if to apply to sample data in your question

with `project.dataset.table` as (
   select 'A' team, 50 budget, date '2021-01-17' date union all
   select 'A', 100, '2021-01-24' union all
   select 'A', 140, '2021-01-31' union all
   select 'A', 250, '2021-02-07' union all
   select 'A', 480, '2021-03-08' union all
   select 'B', 70, '2021-03-15' union all
   select 'B', 90, '2021-03-16' union all
   select 'B', 130, '2021-04-08' union all
   select 'B', 340, '2021-05-17' union all
   select 'B', 680, '2021-06-26' 
 )          

output is