CODEWITHSUNDEEP
sql
Tr3cky
Tr3cky

Reputation: 77

HAVING gives me "column...does not exist" but I see the column

This is a practice question from stratascratch and I'm literally stuck at the final HAVING statement.

Problem statement:

Find the total number of downloads for paying and non-paying users by date. Include only records where non-paying customers have more downloads than paying customers. The output should be sorted by earliest date first and contain 3 columns date, non-paying downloads, paying downloads.

There are three tables:

ms_user_dimension (user_id, acc_id)
ms_acc_dimension (acc_id, paying_customer)
ms_download_facts (date, user_id, downloads)

This is my code so far

SELECT date, 
    SUM(CASE WHEN paying_customer = 'no' THEN cnt END) AS no,
    SUM(CASE WHEN paying_customer = 'yes' THEN cnt END) AS yes
FROM (
    SELECT date, paying_customer, SUM(downloads) AS cnt
    FROM ms_download_facts d
    LEFT JOIN ms_user_dimension u ON d.user_id = u.user_id
    LEFT JOIN ms_acc_dimension a ON u.acc_id = a.acc_id
    GROUP BY 1, 2
    ORDER BY 1, 2
) prePivot
GROUP BY date
HAVING no > yes;

If I remove the HAVING no > yes at the end, the code will run and I can see I have three columns: date, yes, and no. However, if I add the HAVING statement, I get the error "column "no" does not exist...LINE 13: HAVING no > yes"

Can't figure out for the sake of my life what's going on here. Please let me know if anyone figures out something. TIA!

Upvotes: 1

Views: 1200

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You don't need a subquery for this:

SELECT d.date, 
       SUM(CASE WHEN a.paying_customer = 'no' THEN d.downloads END) AS no,
       SUM(CASE WHEN a.paying_customer = 'yes' THEN d.downloads END) AS yes
FROM ms_download_facts d LEFT JOIN
     ms_user_dimension u
     ON d.user_id = u.user_id LEFT JOIN
     ms_acc_dimension a
     ON u.acc_id = a.acc_id
GROUP BY d.date
HAVING SUM(CASE WHEN a.paying_customer = 'no' THEN d.downloads END) >  SUM(CASE WHEN a.paying_customer = 'yes' THEN d.downloads END);

You can simplify the HAVING clause to:

HAVING SUM(CASE WHEN a.paying_customer = 'no' THEN 1 ELSE -1 END) > 0

This version assumes that paying_customer only takes on the values 'yes' and 'no'.

You may be able to simplify the query further, depending on the database you are using.

Upvotes: 1

AndrewGraham
AndrewGraham

Reputation: 310

It doesn't like aliases in the having statement. Replace no with:

SUM(CASE WHEN paying_customer = 'no' THEN cnt END)

and do the similar thing for yes.

SELECT date, 
    SUM(CASE WHEN paying_customer = 'no' THEN cnt END) AS no,
    SUM(CASE WHEN paying_customer = 'yes' THEN cnt END) AS yes
FROM (
    SELECT date, paying_customer, SUM(downloads) AS cnt
    FROM ms_download_facts d
    LEFT JOIN ms_user_dimension u ON d.user_id = u.user_id
    LEFT JOIN ms_acc_dimension a ON u.acc_id = a.acc_id
    GROUP BY 1, 2
    ORDER BY 1, 2
) prePivot
GROUP BY date
HAVING SUM(CASE WHEN paying_customer = 'no' THEN cnt END) > SUM(CASE WHEN paying_customer = 'yes' THEN cnt END);

Upvotes: 0

Related Questions