Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
40
Help us understand the problem. What is going on with this article?
@jnchito

【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
40
Help us understand the problem. What is going on with this article?
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
jnchito
株式会社ソニックガーデンのRubyプログラマ。 「プロを目指す人のためのRuby入門( https://bit.ly/3wmJheK )」の著者。 および「Everyday Rails - RSpecによるRailsテスト入門 ( https://bit.ly/3rKqrKX )」の翻訳者。 プログラミングスクール「フィヨルドブートキャンプ」のメンターでもある。
sonicgarden
「お客様に無駄遣いをさせない受託開発」と「習慣を変えるソフトウェアのサービス」に取り組んでいるソフトウェア企業

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
40
Help us understand the problem. What is going on with this article?