10 Classic SQL Interview Questions with Solutions

Introduction

SQL is the most commonly used language for data extraction and simple preprocessing in daily data work. Because of its wide adoption and ease of learning, it is also frequently used by product managers and developers. This article compiles classic interview questions to provide practical SQL methods and hands‑on solutions for data‑related job interviews.

Difficulty Classification

Simple – Tests basic GROUP BY, LIMIT, rarely used functions such as RAND(), and simple table joins.

Medium – Involves basic window function usage and more intricate table relationships, including self‑joins.

Hard – Requires handling median calculations, complex column generation, and often benefits from creating intermediate tables for clarity.

SQL Interview Questions

Question 1

Given an order table (fields: goods_id, amount) and a pv (page view) table (fields: goods_id, uid), rank goods by total sales amount into three groups: top 10, top 10‑20, and others. Then compute the number of distinct users who viewed goods in each group.

createtableifnotexists test.nil_goods_category asselect goods_id,casewhen nn<=10 then 'top10'      when nn<=20 then 'top10~top20'      else 'other' endas goods_groupfrom(    select goods_id    ,row_number() over(partitionby goods_id orderby sale_sum desc) as nn    from    (        select goods_id,sum(amount) as sale_sum        from order        group by 1    ) aa) bb;select b.goods_group,count(distinct a.uid) as num from pv a left join test.nil_goods_category b on a.goods_id = b.goods_id group by 1;

Question 2

Given a goods_event table (fields: g_id (may duplicate), t1 start time, t2 end time) and a time interval (t3, t4), find the number of distinct goods that have an activity within the interval.

select count(distinct g_id) as event_goods_num from goods_event where (t1<=t4 and t1>=t3) or (t2>=t3 and t2<=t4);

Question 3

From an event table (fields: goods_id, time), retrieve the most recent activity time of the product that participated in activities the most times.

select a.goods_id,a.time from event a inner join (    select goods_id,count(*) from event group by goods_id order by count(*) desc limit 1) b on a.goods_id = b.goods_id order by a.goods_id,a.time desc;

Question 4

From an order table containing order ID and timestamp, select the last three orders of each month’s last day.

select * from (  select * ,rank() over(partitionby mm orderby dd desc) as nn1  ,row_number() over(partitionby mm,dd orderby inserttime desc) as nn2  from  (select cast(right(to_date(inserttime),2) as int) as dd, month(inserttime) as mm, userid, inserttime from koo.nil_temp0222) aa ) bb where nn1 = 1 and nn2<=3;

Question 5

Generate session IDs for user login logs where logins within one hour belong to the same session. The source table koo.nil_temp0222 contains userid and inserttime.

droptable if exists koo.nil_temp0222_a2;createtable if not exists koo.nil_temp0222_a2 as select *,row_number() over(partitionby userid orderby inserttime) as nn1 from (    select a.*, b.inserttime as inserttime_aftr, datediff(b.inserttime,a.inserttime) as session_diff  from (        select userid,inserttime,row_number() over(partitionby userid orderby inserttime asc) nn    from koo.nil_temp0222 where userid = 1900000169 ) a left join (        select userid,inserttime,row_number() over(partitionby userid orderby inserttime asc) nn    from koo.nil_temp0222 where userid = 1900000169 ) b on a.userid = b.userid and a.nn = b.nn-1) a where session_diff > 10 or nn = 1 order by userid,inserttime;droptable if exists koo.nil_temp0222_a2_1;createtable if not exists koo.nil_temp0222_a2_1 as select a.*,case when b.nn is null then a.nn+3 else b.nn end as nn_end from koo.nil_temp0222_a2 a left join koo.nil_temp0222_a2 b on a.userid = b.userid and a.nn1 = b.nn1 - 1;select a.*,b.nn1 as session_id from ( select userid,inserttime,row_number() over(partitionby userid orderby inserttime asc) nn from koo.nil_temp0222 where userid = 1900000169) a left join koo.nil_temp0222_a2_1 b on a.userid = b.userid and a.nn>=b.nn and a.nn<b.nn_end;

Question 6

From a Tourists table that records daily visitor counts for a scenic spot in July 2017 (fields: id, date, visits where id equals the day number), find dates where the visitor count exceeds 100 for three consecutive days.

select a.*,b.num as num2,c.num as num3 from table a left join table b on a.userid = b.userid and a.dt = date_add(b.dt,-1) left join table c on a.userid = c.userid and a.dt = date_add(c.dt,-2) where b.num>100 and a.num>100 and c.num>100;

Question 7

Given two tables A (21 columns: id + d1‑d20, 100 k rows) and B (same schema, 50 k rows), find the IDs in A whose feature rows match a pattern in B. Matching conditions: either all 20 feature columns match exactly, or at most one feature column differs (unknown which).

-- Exact match
select aa.* from ( select *, concat(d1,d2,d3,…,d20) as mmd from table ) aa left join ( select id, concat(d1,d2,d3,…,d20) as mmd from table ) bb on aa.id = bb.id and aa.mmd = bb.mmd;
-- Allow one mismatch
select a.*, sum(d1_jp,d2_jp,…,d20_jp) as same_judge from ( select a.*, case when a.d1 = b.d1 then 1 else 0 end as d1_jp, …, case when a.d20 = b.d20 then 1 else 0 end as d20_jp from table a left join table b on a.id = b.id ) a where sum(d1_jp,…,d20_jp) = 19;

Question 8

For a rating table t (fields: uid, goods_id, star where star is 1‑5), compute the inner product of rating vectors for every pair of distinct users (i.e., sum of the product of their ratings on common goods).

select aa.uid1, aa.uid2, sum(star_multi) as result from (  select a.uid as uid1, b.uid as uid2, a.goods_id, a.star * b.star as star_multi  from t a left join t b on a.goods_id = b.goods_id and a.uid <> b.uid ) aa group by uid1, uid2;

Question 9

Given a table of numbers and their frequencies, calculate the median of the dataset.

select a.*,b.s_mid_n,c.l_mid_n, avg(b.s_mid_n,c.l_mid_n) from (  select case when mod(count(*),2)=0 then count(*)/2 else (count(*)+1)/2 end as s_mid, case when mod(count(*),2)=0 then count(*)/2+1 else (count(*)+1)/2 end as l_mid from table ) a left join ( select id,num,row_number() over(partitionby id orderby num asc) nn from table ) b on a.s_mid = b.nn left join ( select id,num,row_number() over(partitionby id orderby num asc) nn from table ) c on a.l_mid = c.nn;

Question 10

From an order table with fields shop_id, order_time, and order_amount, find shops that have sales in every week of a given month.

select distinct credit_level from (  select credit_level, count(distinct nn) as number from (    select userid, credit_level, inserttime, month(inserttime) as mm, weekofyear(inserttime) as week, dense_rank() over(partitionby credit_level, month(inserttime) orderby weekofyear(inserttime) asc) as nn    from koo.nil_temp0222 where substring(inserttime,1,7) = '2019-12'    order by credit_level, inserttime ) aa group by 1 ) bbwherenumber = (select count(distinct weekofyear(inserttime)) from koo.nil_temp0222 where substring(inserttime,1,7) = '2019-12');