CODEWITHSUNDEEP
sqlamazon-athena
datazang
datazang

Reputation: 1169

How to define the filter in dates?

With the query, I basically want to compare avg_clicks at different time periods and set a filter according to the avg_clicks.

The below query gives us avg_clicks for each shop in January 2020. But I want to see the avg_clicks that is higher than 0 in January 2020.

Question 1: When I add the where avg_clicks > 0 in the query, I am getting the following error: Column 'avg_clicks' cannot be resolved. Where to put the filter? 

SELECT AVG(a.clicks) AS avg_clicks,
         a.shop_id, 
         b.shop_name
FROM 
    (SELECT SUM(clicks_on) AS clicks,
         shop_id,
         date
    FROM X
    WHERE site = ‘com’
            AND date >= CAST('2020-01-01' AS date)
            AND date <= CAST('2020-01-31' AS date)
    GROUP BY  shop_id, date) as a
    JOIN Y as b
    ON a.shop_id = b.shop_id
GROUP BY  a.shop_id, b.shop_name

Question 2: As I wrote, I want to compare two different times. And now, I want to see avg_clicks that is 0 in February 2020.

As a result, the desired output will show me the list of shops that had more than 0 clicks in January, but 0 clicks in February.

Hope I could explain my question. Thanks in advance.

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Start with conditional aggregation:

SELECT shop_id, 
       SUM(CASE WHEN DATE_TRUNC('month', date) = '2020-01-01' THEN clicks_on END)  / COUNT(DISTINCT date) as avg_clicks_jan,
       SUM(CASE WHEN DATE_TRUNC('month', date) = '2020-02-01' THEN clicks_on END)  / COUNT(DISTINCT date) as avg_clicks_feb
FROM X
WHERE site = 'com' AND
      date >= '2020-01-01' AND
      date < '2020-03-01'
GROUP BY shop_id;

I'm not sure what comparison you want to make. But if you want to filter based on the aggregated values, use a HAVING clause.

Upvotes: 0

zealous
zealous

Reputation: 7503

For your Question 1 try to use having clause. Read execution order of SQL statement which gives you a better idea why are you getting avg_clicks() error.

SELECT AVG(a.clicks) AS avg_clicks,
         a.shop_id, 
         b.shop_name
FROM 
    (SELECT SUM(clicks_on) AS clicks,
         shop_id,
         date
    FROM X
    WHERE site = ‘com’
            AND date >= '2020-01-01'
            AND date <= '2020-01-31'
    GROUP BY  shop_id, date) as a
    JOIN Y as b
    ON a.shop_id = b.shop_id
GROUP BY  a.shop_id, b.shop_name
HAVING AVG(a.clicks) > 0

For your Question 2, you can do something like this

SELECT
    shop_id,
    b.shop_name,
    jan_avg_clicks,
    feb_avg_clicks
FROM
(
    SELECT 
        AVG(clicks) AS jan_avg_clicks,
        shop_id
    FROM 
        (
            SELECT 
                SUM(clicks_on) AS clicks,
                shop_id,
                date
            FROM X
            WHERE site = ‘com’
                AND date >= '2020-01-01'
                AND date <= '2020-01-31'
            GROUP BY 
                shop_id, 
                date
        ) as a
    GROUP BY   
        shop_id
    HAVING AVG(clicks) > 0
) jan

join

(
    SELECT 
        AVG(clicks) AS feb_avg_clicks,
        shop_id
    FROM 
        (
            SELECT 
                SUM(clicks_on) AS clicks,
                shop_id,
                date
            FROM X
            WHERE site = ‘com’
                AND date >= '2020-02-01'
                AND date < '2020-03-01'
            GROUP BY 
                shop_id, 
                date
        ) as a
    GROUP BY   
        shop_id
    HAVING AVG(clicks) = 0
) feb
on jan.shop_id = feb.shop_id

join Y as b
on jan.shop_id =  b.shop_id

Upvotes: 1

Related Questions