365 Days of Daily Coding: Day 76

Today was another lazy day. I think I ought to do this challenge for python too cause I have not been making any significant progress as much as I would like to.

I solved a difficult challenge today. I was tempted to solve an easy one just because I wanted to mark this challenge as done. However, this challenge did not take me more than 10 minutes. 🙂

Difficult Challenge

WITH temp AS (
SELECT c.customer_id, c.store_id, SUM(p.amount) AS amt
FROM customer c LEFT JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY c.customer_id, c.store_id),
avg_store AS (
SELECT DISTINCT store_id, AVG(amt) OVER (PARTITION BY store_id) AS store_avg
FROM temp)

SELECT t.customer_id, t.store_id, t.amt AS ltd_spend,
a.store_avg
FROM temp t JOIN avg_store a
ON t.store_id = a.store_id
WHERE t.customer_id IN (1, 100, 101, 200, 201, 300, 301, 400, 401, 500);