SOLVESQL

地域別注文の特徴

SELECT REGION AS "Region"
	   , (SELECT COUNT(DISTINCT ORDER_ID) FROM RECORDS A WHERE Z.REGION = A.REGION AND CATEGORY = 'Furniture') AS Furniture
	   , (SELECT COUNT(DISTINCT ORDER_ID) FROM RECORDS A WHERE Z.REGION = A.REGION AND CATEGORY = 'Office Supplies') AS "Office Supplies"
	   , (SELECT COUNT(DISTINCT ORDER_ID) FROM RECORDS A WHERE Z.REGION = A.REGION AND CATEGORY = 'Technology') AS Technology
FROM RECORDS Z 
GROUP BY REGION 
ORDER BY REGION;

レストランのウェイターのチップ分析

SELECT DAY
       , TIME
       , ROUND(AVG(TIP), 2) AS AVG_TIP
       , ROUND(AVG(SIZE), 2) AS AVG_SIZE
FROM TIPS
GROUP BY DAY,TIME
ORDER BY DAY,TIME;

バミューダ三角地帯に入る宅配便。

SELECT DATE(ORDER_DELIVERED_CARRIER_DATE) AS delivered_carrier_date
	   , COUNT(ORDER_ID) AS orders 
FROM OLIST_ORDERS_DATASET 
WHERE ORDER_DELIVERED_CUSTOMER_DATE IS NULL
AND ORDER_DELIVERED_CARRIER_DATE LIKE '2017-01%' 
GROUP BY DATE(ORDER_DELIVERED_CARRIER_DATE)
ORDER BY ORDER_DELIVERED_CARRIER_DATE;

家具を探して比重の高い日

SELECT 
order_date AS "order_date", Furniture, ROUND((furniture*1.0/ALL_ORDERS*100), 2) AS furniture_pct
FROM
(
SELECT ORDER_DATE
, COUNT(DISTINCT ORDER_ID) ALL_ORDERS
, (SELECT COUNT(DISTINCT ORDER_ID) FROM RECORDS A WHERE CATEGORY = 'Furniture' AND A.ORDER_DATE = Z.ORDER_DATE) AS furniture
FROM RECORDS Z
GROUP BY ORDER_DATE
HAVING ALL_ORDERS >= 10
)
WHERE ROUND((furniture*1.0/ALL_ORDERS*100), 2) >= 40
ORDER BY ROUND((furniture*1.0/ALL_ORDERS*100), 2) DESC , ORDER_DATE;

デパートの日商

SELECT DATE(A.ORDER_PURCHASE_TIMESTAMP) AS dt
       , ROUND(SUM(PAYMENT_VALUE), 2) AS revenue_daily 
FROM OLIST_ORDERS_DATASET A, OLIST_ORDER_PAYMENTS_DATASET B
WHERE A.ORDER_ID = B.ORDER_ID
AND A.ORDER_PURCHASE_TIMESTAMP > '2018-01-01' 
GROUP BY DATE(A.ORDER_PURCHASE_TIMESTAMP)
ORDER BY ROUND(SUM(PAYMENT_VALUE), 2) ;

ショッピングモールの日収とARPU

SELECT DATE(A.order_purchase_timestamp) AS dt
	   , COUNT(DISTINCT B.ORDER_ID) AS pu, ROUND(SUM(B.payment_value), 2) AS revenue_daily
       , ROUND((SUM(B.payment_value) / COUNT(DISTINCT B.ORDER_ID)), 2) AS arppu
FROM olist_orders_dataset A, olist_order_payments_dataset B 
WHERE A.order_id = B.order_id
AND A.order_purchase_timestamp > '2018-01-01'
GROUP BY DATE(A.order_purchase_timestamp)
ORDER BY DATE(A.order_purchase_timestamp);

2つのテーブルのマージ

SELECT A.ATHLETE_ID 
FROM RECORDS A, EVENTS B 
WHERE A.EVENT_ID = B.ID 
AND B.SPORT = 'Golf'
GROUP BY A.ATHLETE_ID;

何人来ましたか。

SELECT * FROM TIPS WHERE MOD(SIZE, 2) = 1;

データ・グループにグループ化

SELECT QUARTET
	   , ROUND(AVG(X), 2) AS x_mean
	   , ROUND(VARIANCE(X), 2) AS x_var
	   , ROUND(AVG(Y), 2) AS y_mean
	   , ROUND(VARIANCE(Y), 2) AS y_var 
FROM POINTS 
GROUP BY QUARTET;

最近オリンピックが開催された都市

SELECT YEAR, UPPER(SUBSTR(CITY, 0, 4)) AS CITY 
FROM GAMES 
WHERE YEAR >= 2000
GROUP BY YEAR, SUBSTR(CITY, 0, 4)
ORDER BY YEAR DESC ;

私たちのプラットフォームに定住している売り手1

SELECT SELLER_ID, COUNT(DISTINCT ORDER_ID) AS ORDERS
FROM OLIST_ORDER_ITEMS_DATASET
GROUP BY SELLER_ID
HAVING COUNT(DISTINCT ORDER_ID) >= 100;

最高の平日を見つける

SELECT day, SUM(TIP) AS tip_daily  
FROM TIPS 
GROUP BY DAY 
ORDER BY SUM(TIP) DESC 
LIMIT 1

指導者とテーブル

SELECT A.EMPLOYEE_ID AS MENTEE_ID
	   , A.NAME AS MENTEE_NAME
       , B.EMPLOYEE_ID AS MENTOR_ID
       , B.NAME AS MENTOR_NAME
FROM 
(SELECT * FROM EMPLOYEES WHERE JOIN_DATE BETWEEN '2021-09-31' AND '2021-12-31') A,
(SELECT * FROM EMPLOYEES WHERE JOIN_DATE <= '2019-12-31') B
WHERE A.DEPARTMENT <> B.DEPARTMENT
GROUP BY MENTEE_ID, MENTOR_ID
ORDER BY MENTEE_ID, MENTOR_ID;

チェックする自転車を探しています

SELECT BIKE_ID 
FROM RENTAL_HISTORY 
WHERE RENT_AT LIKE '2021-01%'
GROUP BY BIKE_ID
HAVING SUM(DISTANCE) >= 50000 ;

最初の注文と最後の注文

SELECT DATE(MIN(ORDER_PURCHASE_TIMESTAMP)) AS FIRST_ORDER_DATE
       , DATE(MAX(ORDER_PURCHASE_TIMESTAMP)) AS LAST_ORDER_DATE 
FROM OLIST_ORDERS_DATASET ;

事前出荷日予測の成功と失敗

SELECT DATE(X.ORDER_PURCHASE_TIMESTAMP) AS PURCHASE_DATE
	   , IFNULL(SUM(CASE WHEN ORDER_ESTIMATED_DELIVERY_DATE >= ORDER_DELIVERED_CUSTOMER_DATE THEN 1 END), 0) AS SUCCESS
	   , IFNULL(SUM(CASE WHEN ORDER_ESTIMATED_DELIVERY_DATE < ORDER_DELIVERED_CUSTOMER_DATE THEN 1 END), 0) AS FAIL
FROM OLIST_ORDERS_DATASET X
WHERE X.ORDER_PURCHASE_TIMESTAMP LIKE '2017-01%'
AND X.ORDER_ESTIMATED_DELIVERY_DATE IS NOT NULL
AND X.ORDER_DELIVERED_CUSTOMER_DATE IS NOT NULL
GROUP BY DATE(X.ORDER_PURCHASE_TIMESTAMP)
ORDER BY DATE(X.ORDER_PURCHASE_TIMESTAMP) ;

大量注文フォームの検索

SELECT TOTAL_BILL, TIP, SEX, SMOKER, DAY, TIME, SIZE 
FROM
(
	SELECT *, AVG(TOTAL_BILL) OVER() AS AVG_BILL 
    FROM TIPS
) WHERE TOTAL_BILL > AVG_BILL;

レストランのコーナー

SELECT * FROM TIPS
WHERE DAY IN (SELECT DAY FROM TIPS GROUP BY DAY HAVING SUM(TOTAL_BILL) >= 1500);

レストラン毎週VIP

SELECT * FROM TIPS
WHERE TOTAL_BILL IN (SELECT MAX(TOTAL_BILL) FROM TIPS GROUP BY DAY);

毎週のダイジェスト

SELECT ROUND(AVG(SUM_TOTAL), 2) FROM
(SELECT SUM(TOTAL_BILL) AS SUM_TOTAL FROM TIPS GROUP BY DAY
);