365 Days of Coding: Day 25

Another successful sql challenge today. Handling dates are rather very important topics in SQL.

Today’s challenge: Given a table called “payment”, I had to find the total daily revenue for the period of June, 2020.

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 Solution:

dt	sum
2020-06-14	57.84
2020-06-15	1376.52

My code:

SELECT DATE(payment_ts) AS dt, sum(amount) AS "sum"
FROM payment
WHERE payment_ts >= date '2020-06-01'
AND payment_ts < date '2020-07-01'
GROUP BY dt
ORDER BY dt;

Another alternative to filter on the dates is as follows:

SELECT DATE(payment_ts) AS dt, sum(amount) AS "sum"
FROM payment
WHERE payment_ts >= to_date('2020/06/01', 'yyyy/mm/dd') 
AND payment_ts < to_date('2020/07/01', 'yyyy/mm/dd')
GROUP BY dt
ORDER BY dt;

• Facebook
• Instagram
• LinkedIn
• GitHub