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

【SQL腕試し問題!】締め切り日が近いイベントの一覧の取得するSQLを書いて下さい

More than 5 years have passed since last update.

はじめに

先日、簡単そうに見えて意外と難しいSQLに遭遇したので、SQLの腕試し問題として出題してみます。
みなさん奮って答えを考えてみて下さい!!

要件

  • イベントとスケジュールという二つのモデル(テーブル)がある。
  • イベントは複数のスケジュールを持つことができる。
  • スケジュールを一件も持たないイベントもある(スケジュール未定のイベント)。
  • スケジュールには締め切り日が設定されている。
    • イベント単位で同じ締め切り日は複数存在しないものとする。
  • 次のような条件でイベントとスケジュールの一覧を出したい。
    • システム日付以降に締め切り日がある、またはスケジュール未定のイベントのみを表示する。
    • スケジュールが複数あるイベントはシステム日付に最も近い締め切り日のスケジュールのみを表示する。
    • 締め切り日順に並び替える(締め切り日が近いほど上)。ただしスケジュール未定であれば一番下に表示する。
    • 締め切り日が同じであればイベントID順に並び替える(IDが小さいものほど上)。

イベントテーブル

id name
1 Completed event
2 No schedule event
3 Continuing event
4 Future event 1
5 Future event 2

スケジュールテーブル

id event_id due_date
1 1 2014/01/23
2 3 2014/01/01
3 3 2014/02/01
4 3 2014/03/01
5 4 2014/01/24
6 4 2014/01/25
7 5 2014/01/24

期待する出力結果(システム日付が 2014/01/24 の場合)

event_id name schedule_id due_date
4 Future event 1 5 2014/01/24
5 Future event 2 7 2014/01/24
3 Continuing event 3 2014/02/01
2 No schedule event

制約等

  • RDBMSはPostgreSQL 9.2.4とする(推奨)。
    • 解答例を比較しやすいようにRDBMSを固定しようと思いましたが、必須条件ではなく推奨条件とします。他のRDBMSを使ってもOKです。SQL方言やRDBMS固有の関数も自由に使って下さい。
  • システム日付は便宜的に以下のSQLで取得できる値とする。
SELECT MAX(sysdate) FROM sysdate_dummy

スキーマ作成用のSQL

CREATE TABLE events (
  id integer PRIMARY KEY
  ,name varchar(50)
);

INSERT INTO events VALUES (1,'Completed event');
INSERT INTO events VALUES (2,'No schedule event');
INSERT INTO events VALUES (3,'Continuing event');
INSERT INTO events VALUES (4,'Future event 1');
INSERT INTO events VALUES (5,'Future event 2');

CREATE TABLE schedules (
  id integer PRIMARY KEY
  ,event_id integer
  ,due_date date
);

INSERT INTO schedules VALUES (1,1,'2014/01/23');
INSERT INTO schedules VALUES (2,3,'2014/01/01');
INSERT INTO schedules VALUES (3,3,'2014/02/01');
INSERT INTO schedules VALUES (4,3,'2014/03/01');
INSERT INTO schedules VALUES (5,4,'2014/01/24');
INSERT INTO schedules VALUES (6,4,'2014/01/25');
INSERT INTO schedules VALUES (7,5,'2014/01/24');

CREATE TABLE sysdate_dummy (
  id integer PRIMARY KEY
  ,sysdate date
);

INSERT INTO sysdate_dummy VALUES (1,'2014/01/24');

解答方法

  • 実行方法は自由ですが、SQL Fiddleを使うとお手軽かもしれません。
  • SQLができたらgistSQL FiddleのURLをこの記事のコメントに 貼り付けて下さい。工夫した点やアピールポイントがあればご自由にどうぞ。

Screen Shot 2014-01-24 at 5.58.31.png

その他

  • 優勝者には素敵な商品が・・・出ません。順位を付ける予定もありません。各自、自己満足の世界でSQLを作って下さい。
  • この課題はとあるRailsの案件で必要になったSQLをベースとしています。Rails開発者の人はActiveRecordを使うならどうするか、というのを考えてみると良いかもしれません。

僕の解答例

僕はこんな感じで作ってみました。うーん、そこそこ複雑ですね~。
あ、自力で答えを考えたい人はまだ見ないで下さいね!!

SQLだけ見たい人はこちらをどうぞ。

最後に

それではみなさん、Let's try!!

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
ユーザーは見つかりませんでした