Forum

Posted by Yuanyuan, Feb. 25, 2022, 10:31 a.m.

#9 Why my code is wrong?

Write a query to count the number of customers who spend more than > $20 by month
my code:
SELECT extract(year from payment_ts)as year, extract(month from payment_ts)as mon, count(distinct(sum(amount)>20))as num_hp_customers
FROM payment
group by year, mon
Also, what is group by 1,2 from correct answer?
Correct code:
SELECT
year,
mon,
COUNT(DISTINCT customer_id)
FROM (
SELECT
EXTRACT(YEAR FROM payment_ts) AS year,
EXTRACT(MONTH FROM payment_ts) AS mon,
customer_id,
SUM(amount) amt
FROM payment
GROUP BY year, mon, customer_id
) X
WHERE amt > 20
GROUP BY 1,2;

Answers

Hi Yuanyuan,
1. Your syntax had some issues, I'd recommend you check out the lecture for aggregation functions.
2. The group by 1, 2 is a shortcut that is equivalent to grouping by the first and second columns.
Hope it helps.
Leon
SQLPad user avatar

Leon (949)

Feb. 25, 2022, 4:44 p.m.

There's no such syntax as count(distinct(sum(amount)>20))

šŸ‘

Lisa, March 4, 2022, 2:52 p.m.
SQLPad user avatar

Eric (5)

March 1, 2022, 5:52 a.m.

is it possible to do a CASE WHEN statement for this? trying to think it through but cant quite get it..

I think it's possible to use CASE WHEN @Dustin,  something like:

COUNT(CASE WHEN SUM(amount) > 20 THEN user_id ELSE NULL END) 

Kerry, March 29, 2022, 11:58 a.m.
SQLPad user avatar

Dustin (5)

March 29, 2022, 7:02 a.m.