SQL for Analytics - lesson 7 | HackIndia Skills

Key point

Trust is earned

A metric is not ready when the query runs. It is ready when you have checked that the number makes sense from at least two angles.

Your project should show these checks. Recruiters and judges may not rerun everything, but they can see that you think like an analyst.

What to check

Check totals, date ranges, duplicates, nulls, and impossible values. Then compare your metric with a simpler query that should roughly agree.

For example, if your funnel says 120 users reached payment, the raw event count for payment_started should not be 20. If your college app has signups before launch date, something is wrong.

Your to-do

Basic check flow

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.

Count rows in each source table before analysis.
Check the minimum and maximum dates used in the query.
Look for duplicate event IDs or duplicate user-event timestamps.
Count nulls in important columns like user_id, event_name, amount, and created_at.
Recompute the final metric with a simpler query and compare.

0 of 5 done

Example

Checks query file

Add a file like checks.sql beside your main queries. Keep it boring and clear.

This example assumes a product_events table with event_id, user_id, event_name, created_at, and amount_inr.

-- checks.sql

-- 1. Row count and date range
SELECT
  COUNT(*) AS total_events,
  MIN(created_at) AS first_event_at,
  MAX(created_at) AS last_event_at
FROM product_events;

-- 2. Null checks for required fields
SELECT
  SUM(CASE WHEN event_id IS NULL THEN 1 ELSE 0 END) AS null_event_ids,
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS null_user_ids,
  SUM(CASE WHEN event_name IS NULL THEN 1 ELSE 0 END) AS null_event_names,
  SUM(CASE WHEN created_at IS NULL THEN 1 ELSE 0 END) AS null_created_at
FROM product_events;

-- 3. Duplicate event IDs
SELECT
  event_id,
  COUNT(*) AS times_seen
FROM product_events
GROUP BY event_id
HAVING COUNT(*) > 1;

-- 4. Impossible payment values
SELECT
  COUNT(*) AS bad_payment_rows
FROM product_events
WHERE event_name = 'payment_success'
  AND (amount_inr IS NULL OR amount_inr <= 0);

-- 5. Simple payment user count for comparison
SELECT
  COUNT(DISTINCT user_id) AS users_with_successful_payment
FROM product_events
WHERE event_name = 'payment_success';

Common mistake

Do not hide mismatches

Beginners often change the query until the number looks nice. Do the opposite. Write down the mismatch and the likely reason.

A 2 percent difference may come from filters, date cutoffs, test users, or duplicate events. A 50 percent difference usually means the query logic is wrong.

Tip

Use AI carefully

You can ask ChatGPT, Gemini, or Claude to review your SQL checks. Paste your table columns, your metric query, and ask: “What sanity checks should I run before presenting this?”

Do not trust generated SQL blindly. Check table names, date functions, join keys, and whether the AI assumed a database you are not using.