SQL for Analytics - lesson 3 | HackIndia Skills

SQL for Analytics - lesson 3 | HackIndia Skills | HackIndia

Example

Sample data

Use a small events table like this in your project. It is enough to practise rankings, totals, and shares.

Each row is one product event from a user.

create table events (
  event_id integer,
  user_id integer,
  event_name text,
  feature text,
  event_time timestamp,
  amount_rs integer
);

insert into events values
(1, 101, 'view',     'payments', '2026-01-10 09:00:00', 0),
(2, 102, 'view',     'chat',     '2026-01-10 09:05:00', 0),
(3, 101, 'pay',      'payments', '2026-01-10 09:10:00', 299),
(4, 103, 'view',     'payments', '2026-01-10 09:20:00', 0),
(5, 104, 'message',  'chat',     '2026-01-10 09:30:00', 0),
(6, 105, 'pay',      'payments', '2026-01-10 10:00:00', 499),
(7, 102, 'message',  'chat',     '2026-01-10 10:05:00', 0),
(8, 106, 'view',     'catalog',  '2026-01-10 10:20:00', 0);

Example

Rank features

This query answers: which features were used most on 10 Jan? It keeps one row per feature, then ranks the features by event count.

rank() can skip numbers after ties. dense_rank() does not skip. For a leaderboard, dense_rank() is often easier to read.

select
  feature,
  count(*) as event_count,
  dense_rank() over (order by count(*) desc) as feature_rank,
  round(
    100.0 * count(*) / sum(count(*)) over (),
    2
  ) as pct_of_events
from events
where event_time >= '2026-01-10'
  and event_time <  '2026-01-11'
group by feature
order by feature_rank, feature;

Example

Running revenue

This query answers: how much revenue had arrived after each payment? It shows the running total in rupees after every pay event.

This is useful for order timelines, daily sales, wallet top-ups, or UPI payment flows in a student startup project.

select
  event_time,
  user_id,
  amount_rs,
  sum(amount_rs) over (
    order by event_time
    rows between unbounded preceding and current row
  ) as running_revenue_rs
from events
where event_name = 'pay'
order by event_time;

Rank and total with windows

One idea

Why windows matter

Sample data

Read the pattern

Rank features

Running revenue

Common mistake

Do this now

Quick check