365 Days of Coding: Day 23

I solved a sqlpad questions today. Somehow, I was only able to do 1 challenge and that seems to be the limit for a free account. I am actually conflicted as to whether to buy their subscription. It is because compared to leetcode they seem to be pricier.

Todays’ challenge: Given a table called “payment”, I had to find out the revenue by 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	rev
2020	1	123.45
2020	2	234.56
2020	3	345.67

My Solution:

SELECT EXTRACT(YEAR FROM payment_ts) as "year", EXTRACT(MONTH FROM payment_ts) as "mon", 
sum(amount) as "rev"
FROM payment
GROUP BY year, mon
ORDER BY year, mon
LIMIT 5;

The oracle documentation on the EXTRACT() function can be found here.

One thing that I always tend to forget is that the “GROUP BY” function is to always be accompanied by an aggregate function after “Select”. Here for instance, there’s a SUM() as the aggregate function.

• Facebook
• Instagram
• LinkedIn
• GitHub