CODEWITHSUNDEEP
mysqlsql
Rahul Raj
Rahul Raj

Reputation: 13

Dynamic SQL query to select Quarters and perform aggregation on the data

sl..   Country  Channel  Type  Clicks    Spend     Impressions    Date       
1.     india    Social   a     14        $25       1,331          2/11/2021     
2.     india    Search   b     1,748     $1,801    1,166,140      2/11/2021     
3.     india    Display  c     28,615    $3,901    8,279,595      2/11/2021     
4.     india    Display  a     1,500     $1,000    1,233          7/10/2020     
5.     india    Display  a     11,500    $500      5,133          10/1/2020     
6.     india    Display  a     599       $200      6570           1/1/2020

So, what I needed to do is to create clicks/impressions for every quarter based on the historical data. The historical data is the same just that the dates are different, now to create (clicks/impressions) for this quarter the value will be

select sum(clicks)/sum(impressions)
from table_name
groupby (country, channel, type)
where ________

I need help in the where clause, I need to select the data from only some specific quarters while generating clicks/impressions for any quarter and the logic to select is:

Sum(clicks(q3 2020,q4 2020, q1 2020))/Sum(impressions(q3 2020,q4 2020, q1 2020))

where we need to find the quarters and the year from the date column. By dynamically I mean if we move to the next quarter then I need to compare the average of the last 2 quarters and the same quarter previous year.

I wrote the code to find the quarters and year from the date, but how to proceed?

CASE

    WHEN EXTRACT(MONTH FROM day) BETWEEN 7 AND 9 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM day) BETWEEN 10 AND 12 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM day) BETWEEN 1 AND 3 THEN 'Q3'
    WHEN EXTRACT(MONTH FROM day) BETWEEN 4 AND 6 THEN 'Q4'
END AS quarter,
EXTRACT(Year FROM day) AS Year

Desired output

slno.  quarter  clicks/impressions
1.     Q1 2021  0.53
2.     Q4 2020  1.35
.......

Upvotes: 0

Views: 1202

Answers (1)

MatBailie
MatBailie

Reputation: 86735

When you're trying to filter data, don't do complex maths on the data and filter the results. That requires processing the whole table, then throwing away the rows not required.

Instead, do the maths in the filter parameters, which will allow the database to fulfil the filtering by checking indexes and only loading the rows it needs.

Fot example...

SELECT
   SUM(clicks) / SUM(impressions)
FROM
    yourTable
WHERE
      (Date >= '2020-07-01' AND Date < '2021-01-01')
   OR (Date >= '2020-01-01' AND Date < '2020-04-01')

That ensures you only process the data for the last two quarters of 2020, plus the first quarter of 2020.

The question then becomes, how to work out those dates based on today's date.

The start of the current quarter can be as provided by this... How do I get the first date of a quarter in MySQL?

If you store the result of that calculation in a variable named @CurrentQuarterStart, the WHERE clause becomes this...

WHERE
      (Date >= @CurrentQuarterStart - INTERVAL 2 QUARTERS AND Date < @CurrentQuarterStart)
   OR (Date >= @CurrentQuarterStart - INTERVAL 4 QUARTERS AND Date < @CurrentQuarterStart - INTERVAL 3 QUARTERS)

Upvotes: 1

Related Questions