Posted by Yuanyuan, Feb. 25, 2022, 10:31 a.m.
#9 Why my code is wrong?
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;