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

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 singular day - it pulls the highest earnings for each day.

  select distinct quarter, order_event_date, max(gc) as highest_day_gc
    from
    (select sum(commission) as cm, order_date,
    extract(quarter from order__date) as quarter
    from order_table
    where advertiser_id ='123'
    and event_year='2018'
    group by 3,2)
    group by 1,2
    order by 2 DESC
sql


Solution 1:[1]

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.

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 kurniadi445
Solution 2

Related Questions