SQL query to get top 24 records, then average the first 12 and bottom 12-CodePudding

I'm attempting to analyze each account's performance (A_Count & B_Count) during their first year versus their second year. This should only return clients who have at least 24 months of totals (records).

Volume Table

Account	ReportDate	A_Count	B_Count
1001A	2019-01-01	47	100
1001A	2019-02-01	50	105
1002A	2019-02-01	50	105

I think I'm on the right track by wanting to grab the top 24 records for each account (only if 24 exist) and then grabbing the top 12 and bottom 12, but not sure how to get there.

I guess ideal output would be:

Account	YR1_A_Avg	YR1_B_Avg	YR2_A_Avg	YR2_B_Avg	FirstDate	LastDate
1001A	47	100	53	115	2019-01-01	2021-12-31
1002A	50	105	65	130	2019-02-01	2022-01-01
1003A	15	180	38	200	2017-05-01	2019-04-01

I'm not too worried about performance.

CodePudding user response：

Assuming there are no gaps in ReportDate (per Account).

select Account 
      ,avg(case when year_index = 1 then A_Count end) as YR1_A_Avg  
      ,avg(case when year_index = 1 then B_Count end) as YR1_B_Avg  
      ,avg(case when year_index = 2 then A_Count end) as YR2_A_Avg  
      ,avg(case when year_index = 2 then B_Count end) as YR2_B_Avg
      ,min(ReportDate) as FirstDate
      ,max(ReportDate) as LastDate
from  
       (
        select *
              ,count(*)     over(partition by Account) as cnt 
              ,(row_number() over(partition by Account order by ReportDate)-1)/12  1 as year_index
        from   Volume
       ) t
where cnt >= 24 and year_index <= 2
group by Account