クーポン配布対象
配点 : 30点難易度 : 3
制限実行時間 : 1000ms制限メモリ使用量 : 128 MB
SQLiteの注意点

TOPSIC SQLでは、SQLの実行環境にSQLiteを使用しています。SQLite以外のRDBMSの文法を使用することはできません。
問題を解く際に注意すべきSQLiteの制約について、以下に記載してありますのでご確認下さい。

  1. NUMERIC、INTEGER、REALは、自身のクラスへの変換が推奨されていますが、変換できなかった場合でも、そのまま格納されます。
  2. 新しくデータを追加した場合、既に格納されているデータの中で ROWID の値が最大のものを探し、それに1を加えた値が新しく追加されるデータの ROWID の値として保存されます。
  3. 外部結合は、左外部結合を行う LEFT OUTER JOIN 句のみ使用できます。
    ※2022年12月以降、LEFT / RIGHT / FULL OUTER JOIN が使用可能となりました。
  4. 指定した日付と時刻のタイムゾーンはUTCとして扱われます。
  5. 日付関数や文字列関数については、SQLiteで固有の関数があります。
  6. テーブル項目の論理名が「◯◯日」となっている場合は、日付項目の時分秒は設定されていません。論理名が「◯◯日時」となっている場合は、日付項目の時分秒まで設定されています。
    • 「受注日」の場合は「2021-01-01」
    • 「受注日時」の場合は「2021-01-01 15:15:15」

その他のSQLiteの制約については、SQLiteの公式ドキュメントをご確認ください。
また、ER図の読み方やSQLiteの関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

自社のECサイトで、「おかえりキャンペーン」の開催にあたり、久しぶりに購入してくれた会員にお礼クーポンを配布したいと考えている。
EC受注データ(EC_ORDERS)より、2023年7月の１カ月間に受注データがあり、2023年7月に入り最初に受注したデータの受注日と1つ前の受注データの受注日の差が1年以上ある会員を抽出して表示しなさい。
　※ただし、EC受注データには2023年7月31日終了時点までのデータが含まれているものとする。

7月最初のデータの受注日が2023年7月15日の場合は、対象となる1つ前の受注データの受注日は2022年7月14日以前からとなる。
また、会員データ(MEMBER_MST)のオプトアウト区分(OPTOUT_TYPE)が、0 の場合は「可」、1 の場合は「不可」、それ以外は「不明」とOPTOUT欄に表示しなさい。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)

  • 2023年7月最初のORDER_DATETIME → ORDER_DATE
  • MEMBER_CODE → CODE
  • OPTOUT_TYPEの値により表示内容を変更 → OPTOUT

表示順

  1. ORDER_DATETIMEの降順
  2. MEMBER_CODEの降順

表示フォーマット

※あくまでフォーマットを示すもので、正解例ではありません。行数も正解とは異なります。

ORDER_DATE CODE OPTOUT
2023-07-31 11:00:00 10370 不可
2023-07-15 23:00:00 10220 不明
2023-07-10 12:00:00 10110 可
2023-07-10 00:00:01 10290 可
2023-07-01 00:00:00 10070 可

ER図 (半角:物理名、全角:論理名)


テーブル定義

テーブル名: EC_ORDERS

主キー 列名 データ型 必須 デフォルト値
✔︎ ORDER_NO VARCHAR ✔︎ NULL
ORDER_DATETIME VARCHAR ✔︎ NULL
MEMBER_CODE VARCHAR ✔︎ NULL
GUEST_FLG INTEGER ✔︎ NULL
LAST_NAME VARCHAR ✔︎ NULL
FIRST_NAME VARCHAR ✔︎ NULL
LAST_NAME_KANA VARCHAR ✔︎ NULL
FIRST_NAME_KANA VARCHAR ✔︎ NULL
EMAIL VARCHAR ✔︎ NULL
POSTAL_CODE VARCHAR NULL
PREFECTURE VARCHAR NULL
ADDRESS1 VARCHAR NULL
ADDRESS2 VARCHAR NULL
ADDRESS3 VARCHAR NULL
PHONE_NUMBER VARCHAR NULL
PAYMENT_METHOD_TYPE VARCHAR ✔︎ NULL
USED_POINT INTEGER NULL
PAYMENT_DATE VARCHAR NULL
PAYMENT_LIMIT_DATE VARCHAR NULL
CANCEL_DATE VARCHAR NULL
ORDER_STATUS INTEGER ✔︎ NULL
UPDATE_USER_NAME VARCHAR ✔︎ NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

テーブル名: MEMBER_MST

主キー 列名 データ型 必須 デフォルト値
✔︎ MEMBER_CODE VARCHAR ✔︎ NULL
LAST_NAME VARCHAR ✔︎ NULL
FIRST_NAME VARCHAR ✔︎ NULL
LAST_NAME_KANA VARCHAR ✔︎ NULL
FIRST_NAME_KANA VARCHAR ✔︎ NULL
LOGIN_ID VARCHAR ✔︎ NULL
EMAIL VARCHAR ✔︎ NULL
PSW VARCHAR ✔︎ NULL
BIRTH_DATE VARCHAR ✔︎ NULL
GENDER INTEGER ✔︎ NULL
OPTOUT_TYPE INTEGER ✔︎ NULL
LOGIN_DATETIME VARCHAR NULL
LOGIN_ERROR_COUNT INTEGER NULL
LOGIN_LOCKED_FLG INTEGER NULL
LATEST_POINT_ACQUIRED_DATE VARCHAR NULL
REST_POINT INTEGER NULL
TEMPORARY_POINT INTEGER NULL
WITHDRAWAL_DATE VARCHAR NULL
UPDATE_USER_NAME VARCHAR ✔︎ NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

サンプルデータ

テーブル名: EC_ORDERS

ORDER_NO ORDER_DATETIME MEMBER_CODE GUEST_FLG LAST_NAME FIRST_NAME LAST_NAME_KANA FIRST_NAME_KANA EMAIL POSTAL_CODE PREFECTURE ADDRESS1 ADDRESS2 ADDRESS3 PHONE_NUMBER PAYMENT_METHOD_TYPE USED_POINT PAYMENT_DATE PAYMENT_LIMIT_DATE CANCEL_DATE ORDER_STATUS UPDATE_USER_NAME USER_UPDATE_DATETIME
J21001 2021-07-10 10:00:00 10110 0 矢後 麻雄 ヤゴ アサオ CNmmWTjnwR@aPwL.com 3300056 埼玉県 さいたま市浦和区 東仲町 ３－３３ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2021-07-10 00:00:00
J22001 2022-06-29 00:00:00 10250 0 弦巻 桃奈 ツルマキ モモナ yu2YOp@7MtcKU.co.jp 3792154 群馬県 前橋市 天川大島町 ３－５ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-06-29 00:00:00
J22002 2022-06-30 00:00:00 10010 0 山田 玲 ヤマダ レイ yamadarei@elfelyan.co.jp 3300043 埼玉県 さいたま市浦和区 大東７－８－９ メゾンヤマタネ2号室 NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-06-30 00:00:00
J22003 2022-07-01 00:00:00 10100 0 金久保 安吾 カナクボ アンゴ cTqeC@iloyQZ.com 0600041 北海道 札幌市中央区 大通東１－１ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-07-01 00:00:00
J22004 2022-07-14 00:00:00 10220 0 平光 暁威 ヒラミツ ギョウイ oUhQ6jBI2@jDO3v.com 3300054 埼玉県 さいたま市浦和区 東岸町 ４－４ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-07-14 00:00:00
J22005 2022-07-30 00:00:00 10370 0 関井 亜門 セキイ アモン IYjR3@nS2X.co.jp 3100001 茨城県 水戸市 上河内町 ４－１０ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-07-30 00:00:00
J22006 2022-07-31 00:00:00 10140 0 本堂 章博 ホンドウ アキヒロ Mo8upZ@wYTvyO.com 3792112 群馬県 前橋市 新井町 ４－３ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-07-31 00:00:00
J22007 2022-06-30 00:00:00 10070 0 犬伏 功実 イヌフシ イサミ S3VWssPb@pJaB.com 3300065 埼玉県 さいたま市浦和区 神 明 ３－９ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-06-30 00:00:00
J22009 2022-05-15 00:00:00 10290 0 鳥原 優太 トリハラ ユウタ hGJo2PI@gDAmiF.co.jp 3300064 埼玉県 さいたま市浦和区 岸 町 ４－８ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-05-15 00:00:00
J22010 2022-05-20 00:00:00 10030 0 見上 諄 ミカミ ジュン jhEGGJ@mk3Fd.com 3792106 群馬県 前橋市 荒子町 ４－５ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2022-05-20 00:00:00
J23001 2023-06-30 23:59:59 10250 0 弦巻 桃奈 ツルマキ モモナ yu2YOp@7MtcKU.co.jp 3792154 群馬県 前橋市 天川大島町 ３－５ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-06-30 23:59:59
J23002 2023-07-01 00:00:00 10010 0 山田 玲 ヤマダ レイ yamadahana@elfelyan.co.jp 3300043 埼玉県 さいたま市浦和区 大東７－８－９ メゾンヤマタネ2号室 NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-01 00:00:00
J23003 2023-07-01 00:00:00 10100 0 金久保 安吾 カナクボ アンゴ cTqeC@iloyQZ.com 0600041 北海道 札幌市中央区 大通東１－１ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-01 00:00:00
J23004 2023-07-01 00:00:00 10030 0 見上 諄 ミカミ ジュン jhEGGJ@mk3Fd.com 3792106 群馬県 前橋市 荒子町 ４－５ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-01 00:00:00
J23005 2023-07-02 00:00:00 10030 0 見上 諄 ミカミ ジュン jhEGGJ@mk3Fd.com 3792106 群馬県 前橋市 荒子町 ４－５ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-02 00:00:00
J23006 2023-07-10 00:00:01 10290 0 鳥原 優太 トリハラ ユウタ hGJo2PI@gDAmiF.co.jp 3300064 埼玉県 さいたま市浦和区 岸 町 ４－８ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-10 00:00:00
J23007 2023-07-10 12:00:00 10110 0 矢後 麻雄 ヤゴ アサオ CNmmWTjnwR@aPwL.com 3300056 埼玉県 さいたま市浦和区 東仲町 ３－３３ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-10 00:00:00
J23008 2023-07-10 10:00:00 10290 0 鳥原 優太 トリハラ ユウタ hGJo2PI@gDAmiF.co.jp 3300064 埼玉県 さいたま市浦和区 岸 町 ４－８ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-10 10:00:00
J23009 2023-07-15 23:00:00 10220 0 平光 暁威 ヒラミツ ギョウイ oUhQ6jBI2@jDO3v.com 3300054 埼玉県 さいたま市浦和区 東岸町 ４－４ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-15 00:00:00
J23010 2023-07-31 11:00:00 10370 0 関井 亜門 セキイ アモン IYjR3@nS2X.co.jp 3100001 茨城県 水戸市 上河内町 ４－１０ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-31 00:00:00
J23011 2023-07-31 23:59:59 10140 0 本堂 章博 ホンドウ アキヒロ Mo8upZ@wYTvyO.com 3792112 群馬県 前橋市 新井町 ４－３ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-31 23:59:59
J23012 2023-07-01 00:00:00 10070 0 犬伏 功実 イヌフシ イサミ S3VWssPb@pJaB.com 3300065 埼玉県 さいたま市浦和区 神 明 ３－９ NULL NULL 3 0 NULL NULL NULL 1 SYSTEM 2023-07-01 00:00:00

テーブル名: MEMBER_MST

MEMBER_CODE LAST_NAME FIRST_NAME LAST_NAME_KANA FIRST_NAME_KANA LOGIN_ID EMAIL PSW BIRTH_DATE GENDER OPTOUT_TYPE LOGIN_DATETIME LOGIN_ERROR_COUNT LOGIN_LOCKED_FLG LATEST_POINT_ACQUIRED_DATE REST_POINT TEMPORARY_POINT WITHDRAWAL_DATE UPDATE_USER_NAME USER_UPDATE_DATETIME
10010 山田 玲 ヤマダ レイ yamadarei@elfelyan.co.jp yamadarei@elfelyan.co.jp 1001 1992-12-24 2 0 2023-07-31 11:00:00 0 0 NULL 0 98 NULL SYSTEM 2023-07-31 11:00:00
10020 山田 太郎 ヤマダ タロウ yamadataro@elfelyan.co.jp yamadataro@elfelyan.co.jp 1002 1992-12-24 1 0 2023-07-31 11:00:00 0 0 NULL 0 98 NULL SYSTEM 2023-07-31 11:00:00
10030 見上 諄 ミカミ ジュン jhEGGJ@mk3Fd.com jhEGGJ@mk3Fd.com 1003 1953-10-28 1 0 2023-07-31 11:00:00 2 8 NULL 85 37 NULL SYSTEM 2023-07-31 11:00:00
10070 犬伏 功実 イヌフシ イサミ S3VWssPb@pJaB.com S3VWssPb@pJaB.com 1007 1956-06-02 1 0 2023-07-31 11:00:00 3 8 NULL 88 41 NULL SYSTEM 2023-07-31 11:00:00
10100 金久保 安吾 カナクボ アンゴ cTqeC@iloyQZ.com cTqeC@iloyQZ.com 1010 1958-07-17 1 0 2023-07-31 11:00:00 3 8 NULL 91 44 NULL SYSTEM 2023-07-31 11:00:00
10110 矢後 麻雄 ヤゴ アサオ CNmmWTjnwR@aPwL.com CNmmWTjnwR@aPwL.com 1011 1959-01-06 1 0 2023-07-31 11:00:00 3 8 NULL 92 44 NULL SYSTEM 2023-07-31 11:00:00
10140 本堂 章博 ホンドウ アキヒロ Mo8upZ@wYTvyO.com Mo8upZ@wYTvyO.com 1014 1961-02-19 1 0 2023-07-31 11:00:00 3 9 NULL 95 47 NULL SYSTEM 2023-07-31 11:00:00
10180 曽根田 凜 ソネダ リン 59PBS9q6@WPi6N.co.jp 59PBS9q6@WPi6N.co.jp 1018 1963-09-25 2 0 2023-07-31 11:00:00 4 9 NULL 99 51 NULL SYSTEM 2023-07-31 11:00:00
10220 平光 暁威 ヒラミツ ギョウイ oUhQ6jBI2@jDO3v.com oUhQ6jBI2@jDO3v.com 1022 1966-04-30 1 2 2023-07-31 11:00:00 4 9 NULL 2 55 NULL SYSTEM 2023-07-31 11:00:00
10250 弦巻 桃奈 ツルマキ モモナ yu2YOp@7MtcKU.co.jp yu2YOp@7MtcKU.co.jp 1025 1968-06-12 2 0 2023-07-31 11:00:00 4 0 NULL 5 58 NULL SYSTEM 2023-07-31 11:00:00
10290 鳥原 優太 トリハラ ユウタ hGJo2PI@gDAmiF.co.jp hGJo2PI@gDAmiF.co.jp 1029 1971-01-16 1 0 2023-07-31 11:00:00 5 0 NULL 9 61 NULL SYSTEM 2023-07-31 11:00:00
10330 方波見 郁也 ホナミ イクヤ xsII7r5IF@QJWwiv.com xsII7r5IF@QJWwiv.com 1033 1973-08-21 1 1 2023-07-31 11:00:00 5 0 NULL 13 65 NULL SYSTEM 2023-07-31 11:00:00
10370 関井 亜門 セキイ アモン IYjR3@nS2X.co.jp IYjR3@nS2X.co.jp 1037 1975-10-05 2 1 2023-07-31 11:00:00 5 1 NULL 16 68 NULL SYSTEM 2023-07-31 11:00:00

Sorry, Japanese Only.