How do I write a query to find highest earning day per quarter?-CodePudding

I need to write a sql code to pull the single, highest-earning day for a certain brand of each quarter of 2018. I have the following but it does not pull a singlular day - it pulls the highest earnings for each day.

  select distinct quarter, order_event_date, max(gc) as highest_day_gc
    from
    (select sum(gross_commission) as gc, order_event_date,
    extract(quarter from order_event_date) as quarter
    from order_aggregation
    where advertiser_id ='123'
    and event_year='2018'
    group by 3,2)
    group by 1,2
    order by 2 DESC

CodePudding user response：

You could add the LIMIT clause at the end of the sentence. Also, change the las ORDER BY clause to ORDER BY highest_day_gc. Something like:

SELECT DISTINCT quarter
               ,order_event_date
               ,max(gc) as highest_day_gc
FROM    (SELECT sum(gross_commission) as gc
               ,order_event_date
               ,extract(quarter from order_event_date) as quarter
         FROM order_aggregation
         WHERE advertiser_id ='123'
               AND event_year='2018'
         GROUP BY 3,2) as subquery
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 1

CodePudding user response：

You can use window functions to find the highest earning day per quarter by using rank().

select rank() over (partition by quarter order by gc desc) as rank, quarter, order_event_date, gc
from (select sum(gross_commission) gc,
             order_event_date,
             extract(quarter from order_event_date) quarter
      from order_aggregation
      where advertiser_id = '123'
        and event_year = '2018'
      group by order_event_date, quarter) a

You could create the query above as view and filter it by using where rank = 1.