Help us understand the problem. What is going on with this article?

【SQL腕試し問題!】入会者数と退会者数を日付ごとに集計するSQLを書いてください

More than 3 years have passed since last update.

はじめに

先日、業務で書いた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ができたらgistSQL FiddleのURLをこの記事のコメントに貼り付けて下さい。工夫した点やアピールポイントがあればご自由にどうぞ。

Screen Shot 2014-01-24 at 5.58.31.png

その他

  • 優勝者には素敵な商品が・・・出ません。順位を付ける予定もありません。各自、自己満足の世界でSQLを作って下さい。

僕の解答例

僕はこんな感じで作ってみました。
ちょっと長いけど、保守性や拡張性はちょっと高いかもしれません。
あ、自力で答えを考えたい人はまだ見ないで下さいね!!

最後に

それではみなさん、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
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした