SQL定番面接問題10問解説

SQL定番面接問題10問解説

以下の問題は、筆者の経験とオンラインで共有された中・高難易度のSQL問題から来ています。

最終更新 2022/05/02 18:23
俊欣 关于数据分析与可视化
読了目安 9 分
カテゴリ
データベース
タグ
データベース

一、概要

データワーカーとして、SQLは日常業務で最もよく使われるデータ抽出・簡単な前処理言語です。その広範な利用と習得の容易さから、プロダクトマネージャーやエンジニアなど他の職種でも広く学ばれています。本記事では、主に代表的な面接問題を交えながら、データ開発の面接を通過するためのSQLの方法と実践を紹介します。以下の問題はすべて、筆者の経験やネット上で共有されている中～高難易度のSQL問題に基づいています。

二、解答の考え方

  • 簡単 — group by や limit などの使い方、あるいは普段あまり使わない関数（rand() など）が出題される場合があります。テーブル間の結合も含まれます。

  • 中程度 — ウィンドウ関数の基本的な使い方が出題されます。テーブル間の結合があり、ややトリッキーな点として自己結合の使用があります。

  • 難しい — 中央値やより複雑な取得概念が出題され、特定の条件に従って列を生成するよう要求される場合があります。一般的にこのような問題は、中間テーブルを作成すると明確に解くことができます。

三、SQL 実践問題

第1問

  • order 注文テーブル、フィールド：goods_id, amount；
  • pv 閲覧テーブル、フィールド：goods_id, uid；
  • goods を総販売金額順に並べ、top10、top10～top20、その他の3グループに分割。

各グループの商品の閲覧ユーザー数を求める（同一グループ内で同一ユーザーは1回のみカウント）。

create table if not exists test.nil_goods_category as
select goods_id
,case when nn<= 10 then 'top10'
      when nn<= 20 then 'top10~top20'
      else 'other' end as goods_group
from
(
    select goods_id
    ,row_number() over(partition by goods_id order by 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;

第2問

商品イベントテーブル goods_event、g_id（重複可能性あり）、t1（開始時間）、t2（終了時間）。

指定された時間範囲（t3,t4）において、イベントが行われている商品数を求める。

1.

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

select count(distinct g_id) as event_goods_num
from goods_event
where (t1<=t4 and t1>=t3)
union all

第3問

商品イベントのトランザクションテーブル、テーブル名 event、フィールド：goods_id, time。

最も多くイベントに参加した商品の、最も最近イベントに参加した時間を求める。

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

第4問

ユーザーログインのlogデータ。sessionを定義する。同じユーザーの1時間以内のログインは1つのsessionとみなす。

session列を生成する。

drop table if exists koo.nil_temp0222_a2;
create table if not exists koo.nil_temp0222_a2 as
select *
    ,row_number() over(partition by userid order by 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(partition by userid order by inserttime asc) nn
    from koo.nil_temp0222
    where userid = 1900000169
  ) a
  left join
  (
     select userid,inserttime
      ,row_number() over(partition by userid order by inserttime asc) nn
    from koo.nil_temp0222
    where userid = 1900000169
  ) b
  on a.userid =  b.userid and a.nn = b.nn-1
) aa
where session_diff >10 or nn = 1
order by userid,inserttime;

drop table if exists koo.nil_temp0222_a2_1;
create table 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(partition by userid order by 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

第5問

注文テーブル。フィールドは注文番号と時間。

毎月の最終日の最後の3件の注文を取得する。

select *
from
(
  select *
  ,rank() over(partition by mm order by dd desc) as nn1
  ,row_number() over(partition by mm,dd order by 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;

第6問

データベーステーブル Tourists には、ある観光地の7月の日別訪問者数が以下のように記録されています。

id date visits 1 2017-07-01 100 …… 非常に偶然ですが、idフィールドはちょうど日付の日にちと一致しています。

ここで、3日連続で訪問者数が100を超える日付を抽出してください。

上記の例の出力は、date 2017-07-01 ……

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

第7問

テーブルAがあります。21列で構成され、最初の列はid、残りの列は特徴フィールドで、列名はd1～d20、全10万行のデータです。

もう一つのテーブルBはパターンテーブルと呼ばれ、Aと同じ構造で、全5万行のデータです。

Aの特徴がBのパターンに一致するデータを見つけ、対応するidを記録してください。

以下の2つの条件を満たす必要があります。

  • すべての特徴列が完全に一致する場合
  • 最大1つの特徴列が一致しなくても、他の19の特徴列が完全に一致する場合（ただし、どの列が一致しないかは不明）
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

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.d2 = b.d2 then 1 else 0 end as d2_jp
  ,case when a.d3 = b.d3 then 1 else 0 end as d3_jp
  ,case when a.d4 = b.d4 then 1 else 0 end as d4_jp
  ,case when a.d5 = b.d5 then 1 else 0 end as d5_jp
  ,case when a.d6 = b.d6 then 1 else 0 end as d6_jp
  ,case when a.d7 = b.d7 then 1 else 0 end as d7_jp
  ,case when a.d8 = b.d8 then 1 else 0 end as d8_jp
  ,case when a.d9 = b.d9 then 1 else 0 end as d9_jp
  ,case when a.d10 = b.d10 then 1 else 0 end as d10_jp
  ,case when a.d20 = b.d20 then 1 else 0 end as d20_jp
  ,case when a.d11 = b.d11 then 1 else 0 end as d11_jp
  ,case when a.d12 = b.d12 then 1 else 0 end as d12_jp
  ,case when a.d13 = b.d13 then 1 else 0 end as d13_jp
  ,case when a.d14 = b.d14 then 1 else 0 end as d14_jp
  ,case when a.d15 = b.d15 then 1 else 0 end as d15_jp
  ,case when a.d16 = b.d16 then 1 else 0 end as d16_jp
  ,case when a.d17 = b.d17 then 1 else 0 end as d17_jp
  ,case when a.d18 = b.d18 then 1 else 0 end as d18_jp
  ,case when a.d19 = b.d19 then 1 else 0 end as d19_jp
  from table a
  left join table b
  on a.id = b.id
) aa
where sum(d1_jp,d2_jp……,d20_jp) = 19

第8問

ユーザーの商品に対する評価を疎ベクトルで表現し、データベーステーブルtに保存します。

  • tのフィールド：uid、goods_id、star。uidはユーザーID
  • goods_idは商品ID、starはユーザーのその商品に対する評価（値は1～5）

ここで、ベクトル同士の内積を計算したいとします。内積の意味は次の通りです。

異なる2人のユーザーが同じ商品群に対して評価を行った場合、各商品の評価値を掛け合わせ、その積を合計します。

例：データベーステーブルに以下のデータがあるとします。

U0 g0 2
U0 g1 4
U1 g0 3
U1 g1 1

計算結果：

U0 U1 23+41=10 ……

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.udi<>b.uid
) aa
group by 1,2

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

第9問

数値とその頻度のテーブルが与えられたとき、この数値群の中央値を求める。

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(partition by id order by num asc) nn
  from table
) b
on a.s_mid = b.nn
left join
(
  select id,num,row_number() over(partition by id order by num asc) nn
  from table
) c
on a.l_mid = c.nn

第10問

テーブル order には3つのフィールドがあります。店舗ID、注文時間、注文金額。

1ヶ月間で毎週売上がある店舗をクエリする。

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(partition by credit_level,month(inserttime) order by 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
) bb
where number = (select count(distinct weekofyear(inserttime))
from koo.nil_temp0222
where substring(inserttime,1,7) = '2019-12')
さらに探索

関連読書

その他の記事
最近の更新 2026/05/25

CodeWF.Markdown：PDFテキストはコピー可能、画像は埋め込み可能。WeChat公式アカウント/知乎/掘金にコピーしてもHTMLソースが表示されない

CodeWF.Markdown と Vex における Markdown のエクスポートと公開コピーの技術実装を共有：MarkdownDocumentExporter、ExportKind、共有画像読み込み、SVG/GIF/WebP のラスタライズ、Word 埋め込みメディアリソース、テキスト選択可能なPDF、Windows CF_HTML リッチHTMLクリップボード、拡張可能なレイアウトテーマ。

続きを読む
最近の更新 2026/05/25

Vex 1.1.0：無料でオープンソースの .NET + Avalonia クロスプラットフォーム Markdown エディター

Vex 1.1.0 の紹介。無料でオープンソースの .NET + Avalonia クロスプラットフォーム Markdown エディターです。動的編集、リアルタイムプレビュー、アウトラインジャンプ、ソースモード、プレビューの更新、検索と置換、テーマとタイポグラフィ、選択可能なテキストの PDF/PNG/Word エクスポート、WeChat 公式アカウントへのコピー、新しいユーザーガイドを特集しています。

続きを読む
最近の更新 2026/05/25

CodeWF.Markdown：Avalonia 12 ベースの Markdown レンダリングコントロール

この記事では、CodeWF.Markdown のリポジトリアドレス、NuGet インストール方法、フルパッケージライン、Lite パッケージライン、リアルタイム編集プレビュー、タイポグラフィテーマ、コードハイライト、画像プレビュー、数式、複数ビューアのカバレッジ、インクリメンタルレンダリング機能について紹介します。

続きを読む
友情リンク
林德熙 YouZack WTM 工具小站 可乐不加冰 加菲的博客 Furion 懒得勤快 迷恋自留地 52ABP dotnet全栈开发 程序设计实验室 江湖人士 King-AbpVnext-Pro