The meaning of life is to explore the world

Postgres SQL skills

Posted on May 31, 2024 By Jason Liu

E.g.1:

SELECT 
	TO_CHAR(DATE_TRUNC('month', TO_TIMESTAMP(epoch_sec)), 'MM/YY') AS month,
	COUNT(CASE WHEN my_val = 3 THEN 1 END) AS matched,
	COUNT(*) AS total
FROM my_tab
GROUP BY month
ORDER BY month

E.g.2:

SELECT
	DATE_TRUNC('month', TO_TIMESTAMP(epoch_sec)) AS month_date,
	json_array_elements( array_col::json ) AS sub_type
FROM my_tab
ORDER BY month_date