優良顧客
配点 : 20点難易度 : 2
制限実行時間 : 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の関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

受注テーブル(ORDERS)より、受注日(ORDER_DATE)が、2023年9月1日から2023年9月30日の1ヵ月間で受注した顧客毎の受注件数、受注金額合計、１回あたりの平均受注金額を集計し表示しなさい。
また、平均受注金額は小数第1位を四捨五入し、端数処理した平均受注金額が高い順に表示しなさい。
ただし、受注件数が5件未満の顧客は対象外とし、下記に示す表示順に従って上位最大5件まで表示しなさい。

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

  • CUST_CODE → CODE
  • CUST_NAME → NAME
  • 顧客毎の受注件数 → CNT
  • 顧客毎の受注金額合計 → TTL_AMT
  • 顧客毎の１回あたりの平均受注金額 → AVG_AMT

表示順

  1. 表示項目CNTの降順
  2. 表示項目AVG_AMTの降順
  3. 表示項目CODEの昇順

表示フォーマット

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

CODE NAME CNT TTL_AMT AVG_AMT
C1001 ABC商会 10 73172 7317
C1005 三協貿易 6 26320 4387
C1003 極東貿易 5 36900 7380
C1004 黒田電気 5 36900 7380

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


テーブル定義

テーブル名: CUSTOMER

主キー 列名 データ型 必須 デフォルト値
✔︎ CUST_CODE VARCHAR ✔︎ NULL
CUST_NAME VARCHAR ✔︎ NULL
CUST_KANA VARCHAR NULL
EMP_CODE VARCHAR ✔︎ NULL
CUST_USER_NAME VARCHAR NULL
CUST_USER_DEP_NAME VARCHAR NULL
CUST_ZIP_CODE VARCHAR NULL
CUST_STATE VARCHAR NULL
CUST_ADDRESS1 VARCHAR NULL
CUST_ADDRESS2 VARCHAR NULL
CUST_TEL VARCHAR NULL
CUST_EMAIL VARCHAR NULL
CUST_AR_FLAG INTEGER NULL
CUST_CLOSE_DATE INTEGER ✔︎ NULL
CUST_PAY_MONTHS INTEGER 1
CUST_PAY_DATES INTEGER NULL
CUST_PAY_METHOD INTEGER 1
UPDATE_USER_NAME VARCHAR NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

テーブル名: ORDERS

主キー 列名 データ型 必須 デフォルト値
✔︎ ORDER_NO VARCHAR ✔︎ NULL
ORDER_DATE VARCHAR ✔︎ NULL
DEPT_CODE VARCHAR ✔︎ NULL
CUST_CODE VARCHAR ✔︎ NULL
EMP_CODE VARCHAR ✔︎ NULL
REQUIRED_DATE VARCHAR NULL
CUSTORDER_NO VARCHAR NULL
WH_CODE VARCHAR ✔︎ NULL
ORDER_AMNT INTEGER ✔︎ 0
CMP_TAX INTEGER ✔︎ 0
SLIP_COMMENT VARCHAR NULL
UPDATE_USER_NAME VARCHAR NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

サンプルデータ

テーブル名: CUSTOMER

CUST_CODE CUST_NAME CUST_KANA EMP_CODE CUST_USER_NAME CUST_USER_DEP_NAME CUST_ZIP_CODE CUST_STATE CUST_ADDRESS1 CUST_ADDRESS2 CUST_TEL CUST_EMAIL CUST_AR_FLAG CUST_CLOSE_DATE CUST_PAY_MONTHS CUST_PAY_DATES CUST_PAY_METHOD UPDATE_USER_NAME USER_UPDATE_DATETIME
C1001 ABC商会 エービーシーショウカイ E002 柴田 涼太 購買部 330-0841 埼玉県 さいたま市大宮区 東町 18010002007 NULL 2 99 0 99 1 SYSTEM 2023-07-04 06:01:40
C1002 小津産業 オズサンギョウ E003 六田 栄一 管理部 332-0031 埼玉県 川口市 青木 18010002009 NULL 2 99 0 99 1 SYSTEM 2023-07-04 06:04:49
C1003 極東貿易 キョクトウボウエキ E003 牛木 陽翔 経理部 365-0002 埼玉県 鴻巣市 赤城 18010002011 NULL 2 99 0 99 1 SYSTEM 2023-07-04 06:04:49
C1004 黒田電気 クロダデンキ E002 板屋 大貴 購買部 350-1164 埼玉県 川越市 青柳 18010002012 NULL 2 99 0 99 1 SYSTEM 2023-07-04 06:04:49
C1005 三協貿易 サンキョウボウエキ E002 北大宮 春飛 経理部 285-0032 千葉県 佐倉市 飯田 18010002001 NULL 2 99 0 99 1 SYSTEM 2023-07-04 06:04:49
C1006 昭栄通商 ショウエイツウショウ E001 高山 岳寿 経理部 270-0023 千葉県 松戸市 八ケ崎 18010002002 NULL 2 20 1 20 1 SYSTEM 2023-07-04 06:04:49
C1007 田中交易 タナカコウエキ E002 中井 信太郎 営業部 325-0103 栃木県 那須塩原市 青木 18010002003 NULL 2 99 0 99 1 SYSTEM 2023-07-04 06:04:49
C1008 東邦物産 トウホウブッサン E002 栗原 陽多地 管理部 370-0811 群馬県 高崎市 相生町 18010002006 NULL 2 99 0 99 1 SYSTEM 2023-07-04 06:04:49
C1009 日新商事 ニッシンショウジ E002 NULL NULL NULL NULL NULL NULL NULL NULL 2 99 0 99 1 NULL 2023-07-04 06:04:49
C1010 日本製菓 ニホンセイカ E003 NULL NULL NULL NULL NULL NULL NULL NULL 2 20 0 99 1 NULL 2023-07-04 06:04:49

テーブル名: ORDERS

ORDER_NO ORDER_DATE DEPT_CODE CUST_CODE EMP_CODE REQUIRED_DATE CUSTORDER_NO WH_CODE ORDER_AMNT CMP_TAX SLIP_COMMENT UPDATE_USER_NAME USER_UPDATE_DATETIME
OD0001 2023-09-01 D100 C1001 E002 NULL NULL 300 6001 600 NULL SYSTEM 2023-10-01 01:01:01
OD0002 2023-09-02 D100 C1001 E002 NULL NULL 200 7655 765 NULL SYSTEM 2023-10-01 01:01:01
OD0003 2023-09-03 D200 C1001 E003 NULL NULL 100 9600 960 NULL SYSTEM 2023-10-01 01:01:01
OD0004 2023-09-04 D300 C1001 E002 NULL NULL 200 4200 420 NULL SYSTEM 2023-10-01 01:01:01
OD0005 2023-09-05 D200 C1001 E003 NULL NULL 200 4600 460 NULL SYSTEM 2023-10-01 01:01:01
OD0006 2023-09-06 D200 C1001 E002 NULL NULL 100 9016 901 NULL SYSTEM 2023-10-01 01:01:01
OD0007 2023-09-07 D100 C1001 E002 NULL NULL 100 8400 840 NULL SYSTEM 2023-10-01 01:01:01
OD0008 2023-09-08 D100 C1001 E001 NULL NULL 100 3000 300 NULL SYSTEM 2023-10-01 01:01:01
OD0009 2023-09-09 D200 C1001 E002 NULL NULL 300 15600 1560 NULL SYSTEM 2023-10-01 01:01:01
OD0010 2023-09-10 D300 C1001 E001 NULL NULL 300 5100 510 NULL SYSTEM 2023-10-01 01:01:01
OD0011 2023-09-11 D100 C1002 E003 NULL NULL 100 4800 480 NULL SYSTEM 2023-10-01 01:01:01
OD0012 2023-09-12 D100 C1002 E002 NULL NULL 200 1000 100 NULL SYSTEM 2023-10-01 01:01:01
OD0013 2023-09-13 D100 C1002 E003 NULL NULL 200 5500 550 NULL SYSTEM 2023-10-01 01:01:01
OD0014 2023-09-14 D100 C1002 E002 NULL NULL 100 14000 1400 NULL SYSTEM 2023-10-01 01:01:01
OD0015 2023-09-15 D100 C1003 E002 NULL NULL 200 7600 760 NULL SYSTEM 2023-10-01 01:01:01
OD0016 2023-09-16 D200 C1003 E003 NULL NULL 100 9600 960 NULL SYSTEM 2023-10-01 01:01:01
OD0017 2023-09-17 D300 C1003 E002 NULL NULL 200 6100 610 NULL SYSTEM 2023-10-01 01:01:01
OD0018 2023-09-18 D200 C1003 E003 NULL NULL 200 4600 460 NULL SYSTEM 2023-10-01 01:01:01
OD0019 2023-09-19 D200 C1003 E002 NULL NULL 100 9000 900 NULL SYSTEM 2023-10-01 01:01:01
OD0020 2023-09-20 D100 C1004 E002 NULL NULL 100 8400 840 NULL SYSTEM 2023-10-01 01:01:01
OD0021 2023-09-21 D100 C1004 E001 NULL NULL 100 3000 300 NULL SYSTEM 2023-10-01 01:01:01
OD0022 2023-09-22 D200 C1004 E002 NULL NULL 300 15600 1560 NULL SYSTEM 2023-10-01 01:01:01
OD0023 2023-09-23 D300 C1004 E001 NULL NULL 300 5100 510 NULL SYSTEM 2023-10-01 01:01:01
OD0024 2023-09-24 D100 C1004 E003 NULL NULL 100 4800 480 NULL SYSTEM 2023-10-01 01:01:01
OD0025 2023-09-25 D100 C1005 E002 NULL NULL 200 1000 100 NULL SYSTEM 2023-10-01 01:01:01
OD0026 2023-09-26 D100 C1005 E003 NULL NULL 200 5500 550 NULL SYSTEM 2023-10-01 01:01:01
OD0027 2023-09-27 D100 C1005 E002 NULL NULL 100 14000 1400 NULL SYSTEM 2023-10-01 01:01:01
OD0028 2023-09-28 D100 C1005 E002 NULL NULL 100 1800 180 NULL SYSTEM 2023-10-01 01:01:01
OD0029 2023-09-29 D100 C1005 E002 NULL NULL 100 1200 120 NULL SYSTEM 2023-10-01 01:01:01
OD0030 2023-09-30 D100 C1005 E001 NULL NULL 100 2820 282 NULL SYSTEM 2023-10-01 01:01:01
OD0031 2023-10-01 D200 C1002 E003 NULL NULL 200 6800 680 NULL SYSTEM 2023-10-01 01:01:01
OD0032 2023-08-31 D100 C1001 E002 NULL NULL 200 7600 760 NULL SYSTEM 2023-10-01 01:01:01

Sorry, Japanese Only.