【SQL】データサイエンス100本ノック（構造化データ加工編）に挑戦！！

バックエンド SQL

こんにちは、ドイです！
今回は、今まで挑戦しようと思ってなかなか実行に移せなかったデータサイエンス100本ノックに挑戦しようと思います！！

データサイエンス100本ノック（構造化データ加工編）とは

「データサイエンティスト協会スキル定義委員」の提供する、データサイエンス100本ノック（構造化データ加工編）を実践するための演習問題とデータ、および環境構築のためのスクリプト一式です。SQL、Python、Rの3つの言語で実行することができます。
環境を用意すればわかりますが、それぞれの言語の実行環境、ER図、データなどが用意されています。

こちらから落としてきます。
github.com

ER図

始め方

  1. git経由でコードを落としていて、dockerを立ち上げます。
  2. http://localhost:8888にアクセスします。
  3. 左側メニューからEntity_Relationship.ipynbを開いて、ER図を確認します。
  4. preprocess_knock_SQL.ipynb/ preprocess_knock_Python.ipynb/ preprocess_knock_R.ipynbから実行したい言語のファイルを開きます。
  5. 「%%sql」と打ち込んで、それぞれ言語に対応したコードを入力すれば実行されます。

今回は、それぞれの問題、解答を記述していきたいと思います。

S-001 レシート明細データ（receipt）から全項目の先頭10件を表示し、どのようなデータを保有しているか目視で確認せよ。

%%sql
SELECT * FROM receipt LIMIT 10;

S-002: レシート明細データ（receipt）から売上年月日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。

%%sql
SELECT sales_ymd, customer_id, product_cd, amount FROM receipt LIMIT 10;

S-003: レシート明細データ（receipt）から売上年月日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。ただし、sales_ymd sales_dateに項目名を変更しながら抽出すること。

%%sql
SELECT sales_ymd AS sales_date, customer_id, product_cd, amount FROM receipt LIMIT 10;

S-004: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。

顧客ID（customer_id）が"CS018205000001"

%%sql
SELECT sales_ymd AS sales_date, customer_id, product_cd, amount FROM receipt WHERE customer_id = 'CS018205000001';

S-005: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

顧客ID（customer_id）が"CS018205000001" 売上金額（amount）が1,000以上

%%sql
SELECT sales_ymd AS sales_date, customer_id, product_cd, amount FROM receipt WHERE customer_id = 'CS018205000001' AND amount >= 1000;

S-006: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

顧客ID（customer_id）が"CS018205000001" 売上金額（amount）が1,000以上または売上数量（quantity）が5以上

%%sql
SELECT sales_ymd AS sales_date, customer_id, product_cd, quantity, amount FROM receipt WHERE customer_id = 'CS018205000001' AND (amount >= 1000 or quantity >= 5);

S-007: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

顧客ID（customer_id）が"CS018205000001" 売上金額（amount）が1,000以上2,000以下

%%sql
SELECT sales_ymd AS sales_date, customer_id, product_cd, amount FROM receipt WHERE customer_id = 'CS018205000001' AND amount BETWEEN 1000 AND 2000;

S-008: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

顧客ID（customer_id）が"CS018205000001" 商品コード（product_cd）が"P071401019"以外

%%sql
SELECT sales_ymd AS sales_date, customer_id, product_cd, amount FROM receipt WHERE customer_id = 'CS018205000001' AND product_cd != 'P071401019';

S-009: 以下の処理において、出力結果を変えずにORをANDに書き換えよ。

SELECT * FROM store WHERE NOT (prefecture_cd = '13' OR floor_area > 900)

%%sql
SELECT * FROM store WHERE prefecture_cd != '13' AND floor_area <= 900;

S-010: 店舗データ（store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件表示せよ。

%%sql
SELECT * FROM store WHERE store_cd LIKE 'S14%' LIMIT 10;

まとめ

今回は、導入を含めたため10問までにしたいと思います。
次回からはどんどん問題を解いていき、SQL力を鍛えていきたいと思います！！