365 Days of Daily Coding: Day 27

I am quite not feeling well today. I think I might have a stiff neck. I am planning to see a acupuncturist that my friend has been seeing for a while. The clinic is called Xing Lin Tcm Centre. I hope there isn’t much issue with my body. I am kinda nervous.

Today, I solved a medium challenge questions which is becoming relatively easy. It’s either that or the questions are comparatively easier compared to the challenges in leetcode or hackerrank.

Given a table called “payment”, I had to find out the average amount customer spent per year and month.

Table: payment

col_name	col_type
payment_id	integer
customer_id	smallint
staff_id	smallint
rental_id	integer
amount	numeric
payment_ts	timestamp with time zone

Expected Output:

year	mon	avg_spend
2020	1	143.56
2020	2	2345.9

My solution:

SELECT EXTRACT(YEAR FROM payment_ts) "year", EXTRACT(MONTH FROM payment_ts) mon,
SUM(amount)/COUNT(DISTINCT customer_id) avg_spend
FROM payment
GROUP BY year, mon;

• Facebook
• Instagram
• LinkedIn
• GitHub