Tiktok | Data Scientist | Phone Screen | SQL
hvu26892
1375
ByteDanceTikTokDatabaseInterview

Hello, sharing an sql question in a recent interview I had with TikTok for Data Scientist position:

tiktok_req table
ID          
DAY (YYYY-MM-DD)
PREDICTED (takes value of 1 or 0)
ACTUAL (takes value of 1 or 0)

Key is ID

Question is: compute the precision, recall for each week, and the precision change week over week, recall change week over week from the table. Below is my answer

SELECT main.*,
	(LEAD(main.precision,1) OVER (ORDER BY `week`))/main.precision -1 as precision_change,
	(LEAD(main.recall,1) OVER (ORDER BY `week`))/main.recall -1 as recall_change
FROM
(
		SELECT EXTRACT(WEEK from R.DATE) as `week`,
			SUM(CASE WHEN PREDICTED=ACTUAL THEN 1 ELSE 0 END)/SUM(PREDICTED) as `precision`,
			SUM(CASE WHEN PREDICTED=ACTUAL THEN 1 ELSE 0 END)/SUM(ACTUAL) as `recall`
		FROM tiktok_req R
		GROUP BY 1
) main
Comments (2)