##はじめに
先日、業務で書いたSQLを問題として出してみます。
一見簡単そうに見えて、意外と難しい問題かもしれません。
みなさんはさらっとSQLで集計できるでしょうか！？
##要件
- ユーザーテーブル(users)は入会日(joined_on)と退会日(left_on)を持っている
- 退会していないユーザーの場合、退会日にはNULLが入る
- ユーザー数の増減を確認するために、日付単位で入会したユーザーの人数と退会したユーザーの人数を一覧化したい。どうすれば取得できるか？
##例
###ユーザーテーブル(users)
| id | joined_on | left_on |
|---|---|---|
| 1 | 2014-08-01 | 2014-08-10 |
| 2 | 2014-08-01 | 2014-08-05 |
| 3 | 2014-08-03 | NULL |
| 4 | 2014-08-03 | 2014-08-10 |
| 5 | 2014-08-10 | NULL |
###期待する出力結果
| date | joined_count | left_count |
|---|---|---|
| 2014-08-01 | 2 | 0 |
| 2014-08-03 | 2 | 0 |
| 2014-08-05 | 0 | 1 |
| 2014-08-10 | 1 | 2 |
制約等
- 推奨RDBMSはPostgreSQL 9.2.4ですが、それ以外のRDBMSを使ってもOKです。
スキーマ作成用のSQL
CREATE TABLE users (
id INTEGER PRIMARY KEY,
joined_on DATE NOT NULL,
left_on DATE NULL
);
INSERT INTO users VALUES (1, '2014-08-01', '2014-08-10');
INSERT INTO users VALUES (2, '2014-08-01', '2014-08-05');
INSERT INTO users VALUES (3, '2014-08-03', NULL);
INSERT INTO users VALUES (4, '2014-08-03', '2014-08-10');
INSERT INTO users VALUES (5, '2014-08-10', NULL);
解答方法
- 実行方法は自由ですが、SQL Fiddleを使うとお手軽かもしれません。
- SQLができたらgistやSQL FiddleのURLをこの記事のコメントに貼り付けて下さい。工夫した点やアピールポイントがあればご自由にどうぞ。
その他
- 優勝者には素敵な商品が・・・出ません。順位を付ける予定もありません。各自、自己満足の世界でSQLを作って下さい。
僕の解答例
僕はこんな感じで作ってみました。
ちょっと長いけど、保守性や拡張性はちょっと高いかもしれません。
あ、自力で答えを考えたい人はまだ見ないで下さいね！！
- http://sqlfiddle.com/#!12/c861a/1
- SQL Fiddleが動かない場合はこちらのgistを開いてください。
最後に
それではみなさん、Let's try!!
過去に作ったSQL腕試し問題
もっとSQL問題を解いてみたい！という方はこちらの問題をどうぞ。
##2014.08.22 18:00追記: 応用問題
その日時点での会員数を表示させてみるのも面白いかも。
こちらも解答待ってます！！
| date | joined_count | left_count | user_count |
|---|---|---|---|
| 2014-08-01 | 2 | 0 | 2 |
| 2014-08-03 | 2 | 0 | 4 |
| 2014-08-05 | 0 | 1 | 3 |
| 2014-08-10 | 1 | 2 | 2 |