-- Created on savesnippets.com · https://savesnippets.com/9LHaMfHgu0yiBK
-- Daily signups for the last 30 days
SELECT date_trunc('day',  created_at) AS day,
       COUNT(*)                       AS signups
FROM   users
WHERE  created_at >= NOW() - INTERVAL '30 days'
GROUP  BY day
ORDER  BY day;

-- Hour-of-day analysis
SELECT date_trunc('hour', created_at) AS hour,
       COUNT(*) AS events
FROM   events
WHERE  created_at >= NOW() - INTERVAL '24 hours'
GROUP  BY hour
ORDER  BY hour;

-- Common precisions: 'minute' 'hour' 'day' 'week' 'month' 'quarter' 'year'
-- Weeks are ISO weeks (Monday start) in PostgreSQL.

-- MySQL equivalent — DATE() / DATE_FORMAT()
SELECT DATE(created_at)               AS day, COUNT(*) FROM users GROUP BY day;
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) FROM users GROUP BY month;

-- For arbitrary bucket widths, use generate_series + date_trunc:
SELECT bucket, COUNT(e.id) AS events
FROM generate_series(date_trunc('hour', NOW() - INTERVAL '24h'), NOW(), INTERVAL '15 minutes') AS bucket
LEFT JOIN events e ON e.ts >= bucket AND e.ts < bucket + INTERVAL '15 minutes'
GROUP BY bucket ORDER BY bucket;