365 Days of Coding: Day 28

Today’s medium sql challenge was comparatively challenging. I had fun solving the challenge as it made me go back to some of the materials that I studied especially on the order of operations. If you would like to check out the articles, the link is here.

Challenge: Given the table called “payment”, I had to find out the total number of customers that spent more than $20 in a month.

Table: payment

Expected Solution:

My Solution:

SELECT year, mon, sum(count)
FROM (
SELECT EXTRACT(YEAR FROM payment_ts) AS year,
EXTRACT(MONTH FROM payment_ts) AS mon,
COUNT(DISTINCT customer_id) AS count
FROM payment
GROUP BY year, mon, customer_id
HAVING sum(amount)>20) AS sub
GROUP BY year, mon;

• Facebook
• Instagram
• LinkedIn
• GitHub