SQL for Analytics - lesson 5 | HackIndia Skills

Key point

One date rule

Keep the raw event timestamp as your source of truth. Create date, week, or month fields in the query when you need them.

Most wrong product numbers come from unclear time zones, half-open days, or grouping by the full timestamp instead of the date.

Know your columns

Product events usually have an event timestamp, like created_at or event_time. That is the exact moment an event happened.

For analytics, you often need a calendar day, week, or month. These are derived from the timestamp, not stored as a replacement.

Your to-do

Choose the grain

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Use day for daily active users, orders, signups, and streaks.
Use week for college timetable-friendly reports and trend checks.
Use month for revenue, retention summaries, and founder updates.
Name derived columns clearly, like event_date, event_week, or event_month.
Group by the derived date column, not the raw timestamp.

0 of 5 done

Common mistake

Bad date filters

Do not filter a full day with event_time = '2026-01-10'. A timestamp includes hours, minutes, and seconds, so this will miss most rows.

Use a start time and an end time. The end should be exclusive. This avoids bugs at 23:59:59 and works cleanly for weeks and months too.

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;

Tip

Handle India time

If your app stores UTC timestamps, decide whether the product question needs UTC or India time. A UPI payments dashboard for Indian users usually needs India time.

Write the choice in your project explanation. For example: “All daily metrics use Asia/Kolkata calendar days.”