SQL for Analytics - lesson 5 | HackIndia Skills

SQL for Analytics - lesson 5 | HackIndia Skills | HackIndia

Example

Daily signups

This query counts signups per day for January 2026. It keeps the filter on the timestamp, then groups by the date.

This style works in PostgreSQL. If your database has different date functions, keep the same idea.

SELECT
  DATE(created_at) AS signup_date,
  COUNT(*) AS signups
FROM users
WHERE created_at >= TIMESTAMP '2026-01-01'
  AND created_at <  TIMESTAMP '2026-02-01'
GROUP BY DATE(created_at)
ORDER BY signup_date;

Example

Weekly orders

Weeks are useful when daily numbers are noisy. For hackathon projects, week-level charts are easier to explain than 30 small daily bars.

This query groups paid orders by week and checks revenue in rupees.

SELECT
  DATE_TRUNC('week', paid_at) AS paid_week,
  COUNT(*) AS paid_orders,
  SUM(amount_rupees) AS revenue_rupees
FROM orders
WHERE paid_at >= TIMESTAMP '2026-01-01'
  AND paid_at <  TIMESTAMP '2026-04-01'
GROUP BY DATE_TRUNC('week', paid_at)
ORDER BY paid_week;

Handle dates correctly

One date rule

Know your columns

Choose the grain

Bad date filters

Daily signups

Handle India time

Weekly orders

Do this now

Quick check