SQL Interview Patterns You Must Know

SQL rounds are not about obscure syntax. Interviewers reuse about six patterns. If you can recognise the pattern behind the question, you can write the query. This article walks through each one with a small, concrete example.

Pattern 1 — Rank and pick the top N per group

The question sounds like: *'Find the top 3 cell sites by dropped calls in each region.'* The giveaway words are per or each. That means a window function partitioned by that group.

select region, cell_site_id, dropped_calls
from (
  select
    region,
    cell_site_id,
    dropped_calls,
    row_number() over (
      partition by region
      order by dropped_calls desc
    ) as rn
  from network_kpi_daily
  where event_date = '2026-03-01'
) t
where rn <= 3;

Know the difference between the three ranking functions — this is a very common follow-up:

Pattern 2 — Deduplicate, keeping the latest row

Very common in real pipelines: a source sends the same record several times, and you want only the newest version of each key. This is the same window trick with row_number().

-- Keep the most recent record per subscriber
select * except(rn)
from (
  select
    *,
    row_number() over (
      partition by subscriber_id
      order by updated_at desc, ingestion_ts desc
    ) as rn
  from raw.subscriber_events
) t
where rn = 1;

Pattern 3 — Running totals and moving averages

Any question with *'cumulative'*, *'running'*, or *'7-day average'* is a window function with a frame clause.

select
  event_date,
  cell_site_id,
  data_volume_gb,

  -- running total from the beginning of time
  sum(data_volume_gb) over (
    partition by cell_site_id
    order by event_date
    rows between unbounded preceding and current row
  ) as cumulative_gb,

  -- 7-day moving average (today + 6 previous days)
  avg(data_volume_gb) over (
    partition by cell_site_id
    order by event_date
    rows between 6 preceding and current row
  ) as avg_7d

from network_kpi_daily;

Pattern 4 — Comparing a row to the previous one

Questions like *'find days where traffic dropped more than 50% versus the day before'* use lag() (previous row) or lead() (next row).

with daily as (
  select
    cell_site_id,
    event_date,
    data_volume_gb,
    lag(data_volume_gb) over (
      partition by cell_site_id order by event_date
    ) as prev_day_gb
  from network_kpi_daily
)
select *
from daily
where prev_day_gb > 0
  and data_volume_gb < prev_day_gb * 0.5;

Pattern 5 — Gaps and islands (consecutive runs)

This is the classic hard one: *'find periods where a cell site was down for 3 or more consecutive days.'* The trick is that for a consecutive run, date - row_number() stays constant. Group by that constant.

with flagged as (
  select
    cell_site_id,
    event_date,
    -- for consecutive dates, this expression is the SAME value
    date_sub(event_date, interval
      row_number() over (partition by cell_site_id order by event_date) day
    ) as grp
  from outage_days
)
select
  cell_site_id,
  min(event_date) as outage_start,
  max(event_date) as outage_end,
  count(*)        as consecutive_days
from flagged
group by cell_site_id, grp
having count(*) >= 3;

Pattern 6 — Joins, and the traps inside them

Practice questions