SQL 하나로 자동화

1. 오늘 가입자 수

SELECT COUNT(*) FILTER (WHERE date_joined::date = CURRENT_DATE) FROM accounts_user;

2. 7일 활동량

SELECT d::date, COUNT(DISTINCT user_id) FROM generate_series(CURRENT_DATE-6, CURRENT_DATE, '1 day') d LEFT JOIN sessions s ON s.completed_at::date = d GROUP BY d;

3. 테이블 크기 TOP 10

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

4. 중복 탐지

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

5. 월별 추이

SELECT TO_CHAR(created_at, 'YYYY-MM'), COUNT(*), SUM(amount) FROM orders GROUP BY 1 ORDER BY 1;

실제 서비스 운영에서 매일 쓰는 쿼리들.