# sql宿題提出

・角田さんが作成

## 問題1-1
一日のコメント人数が20人以上でギフト人数が2以下のライバーを最新順に抽出する（DailyKpiSummaries）

SELECT
	l.liverId,
	name,
	rank,
	registeredAt,
	dls.commentUu as comentUu,
	dls.itemUu as commentUu
FROM Livers l
JOIN DailyLiverSummaries dls ON l.liverId = ¢dls.liverId
ORDER BY registeredAt


## 問題1-2
Kanamiの2019年7月以降のデイリーの応援ポイント・配信時間の推移を応援ポイント・配信時間の多い順に抽出する（DailyKpiSummaries

SELECT
	l.liverId,
	l.name,
	l.rank,
	dls.cheerPoint,
	dls.liveTime,
	dls.on
FROM Livers l
JOIN DailyLiverSummaries dls ON dls.on > '2019-07-01'
WHERE
	l.name = '🐰Kanami♡🙏🏺ポコクロック'
ORDER BY l.rank desc,
		 dls.liveTime desc


## 問題2-1
7月新規ライバーの月間配信日数ランキングを作る（MonthlyKPISummariesなど）

SELECT  
	l.liverId,
	l.name,
	l.registeredAt as '登録日',
	mks.liveDayCount
FROM Livers l
JOIN MonthlyKpiSummaries as mks ON mks.liverId = l.liverId AND mks.monthEndsOn = '2019-07-31'
WHERE
	registeredAt BETWEEN '2019-07-01' AND '2019-07-31'
ORDER BY mks.liveDayCount desc


## 問題2-2
各ライバー毎に7/1と8/1の獲得★バッジ数を比較する（badge_extended）

・badge_extendedテーブルが消えていた？

SELECT
	l.liverId,
	l.name,
	@tBOJ1 := 
		dks1.badges -> '$.total."1"' + dks1.badges -> '$.total."2"' + dks1.badges -> '$.total."3"' + dks1.badges -> '$.total."4"' + dks1.badges -> '$.total."5"' as totalBadgeOnJuly1th,
	@tBOA1 :=
		dks2.badges -> '$.total."1"' + dks2.badges -> '$.total."2"' + dks2.badges -> '$.total."3"' + dks2.badges -> '$.total."4"' + dks2.badges -> '$.total."5"' as totalBadgeOnAugust1th,
	(CASE 
		WHEN @tBOJ1 = 0 THEN @tBOA1
		WHEN @tBOJ1 >= 1 THEN  (SELECT TRUNCATE( (@tBOA1 /@tBOJ1) , 2))
		ELSE 0
	END) as ratio
FROM
	Livers l
INNER JOIN
	DailyKpiSummaries dks1 on dks1.liverId = l.liverId AND dks1.onDay = '2019-07-01'
INNER JOIN
	DailyKpiSummaries dks2 on dks2.liverId = l.liverId AND dks2.onDay = '2019-08-01'
ORDER BY
	ratio desc


## 問題2-3
東京に住んでそうなライバーを抽出する（Livers）※答えは色々あります

SELECT
	l.liverId,
	l.name,
	l.place
FROM
	Livers l
WHERE
	l.place LIKE '%東京%'


## 問題3-1
7月のライバー別時間ダイヤ・盛り上がりダイヤの集計※ダイヤ合計（時間別＋盛り上がりダイヤ）は今回の宿題では出さなくて良い"

SELECT
	sum(dgd.timeDiamond) + sum(dgd.bonusDiamond) as 7月合計ダイヤモンド,
	l.name,
	l.liverId
FROM
	DailyGainedDiamonds dgd
JOIN
	Livers l ON l.liverId = dgd.liverId
WHERE
	dgd.on BETWEEN '2019-07-01' AND '2019-07-31'
GROUP BY
	l.name


## 問題3-2
7月ラスト一週間で★1バッジをデイリーで3個以上取得できたことがあるライバーを知りたい

SELECT
	l.name,
	l.liverId,
	MAX(dks.badges -> '$.total."1"') as star1
FROM
	DailyKpiSummaries dks
JOIN
	Livers l ON l.liverId = dks.liverId
WHERE
	dks.onDay BETWEEN ((SELECT LAST_DAY( '2019-07-01' )) - INTERVAL 7 DAY ) AND (SELECT LAST_DAY( '2019-07-01' )) AND
	dks.badges -> '$.total."1"' >= 3
GROUP BY
	l.name
ORDER BY
	star1 desc


## 問題4-1
"4-1: 5月新規ライバー一覧を下記KPIを抽出する（5月新規はSPRINGのシート参照）
https://docs.google.com/spreadsheets/d/1fVic4e2ovTBMeg5WSDpvxMYmRQP80PXd4bifj_zt1VA/edit#gid=339238425
翌月末ランク
初回配信日
最終配信日
目標配信時間クリアフラグ（一ヶ月40時間以上配信した人、一ヶ月60時間以上配信した人を区別できるようなフラグを立てる）
5月ダイヤ合計
6月ダイヤ合計
6月のダイヤ先月比（5月に対して6月は何％伸びた・減ったか）"

・目標配信時間クリアフラグのみデータの場所がわからずできなかった。

SELECT
	l1.liverId,
	l1.name,
	MIN(dlrh.afterLiverRank) as endOfNextMonthRank,
	MAX(l2.finishedAt) as lastBroadcastDay,
	MIN(l2.finishedAt) as firstBroadcastDay,
	MAX(dgd1.bonusDiamond) + MAX(dgd1.coordinationDiamond) + MAX(dgd1.timeDiamond) as diamondSumOnMay,
	MAX(dgd2.bonusDiamond) + MAX(dgd2.coordinationDiamond) + MAX(dgd2.timeDiamond) as diamondSumOnJun,
	( SELECT TRUNCATE(
		(
			(MAX(dgd2.bonusDiamond) + MAX(dgd2.coordinationDiamond) + MAX(dgd2.timeDiamond)) /
			(MAX(dgd1.bonusDiamond) + MAX(dgd1.coordinationDiamond) + MAX(dgd1.timeDiamond))
		), 2)
	) as ratio
	
FROM
	Livers l1
INNER JOIN
	Lives l2 on l1.liverId = l2.liverId
INNER JOIN	
	DailyGainedDiamonds dgd1 on dgd1.liverId = l1.liverId AND dgd1.on BETWEEN '2019-05-01' AND '2019-05-31'
INNER JOIN	
	DailyGainedDiamonds dgd2 on dgd2.liverId = l1.liverId AND dgd2.on BETWEEN '2019-06-01' AND '2019-06-31'
INNER JOIN
	DailyLiverRankHistories dlrh on dlrh.liverId = l1.liverId AND dlrh.on < '2019-07-01'
WHERE
	l1.registeredAt BETWEEN '2019-05-01' AND '2019-05-31'
GROUP BY
	l1.liverId