Trying to calculate CTR through MySQL. Nothing I tried so far has worked
Viewed 3 times
I have been using stackoverflow for years and this is the first time I ask a question and I really hope I can get help with it.
I found this question online while trying to prepare for an interview and I honestly tried everything I can but can't solve it.
The problem is I can do each part alone, like I can create the score_bins column and I can calculate the CTR. However, I am unable to join my answer from both tables.
Can someone please explain the solution to me?
Table A: User_Scores
- UserID, UpdatedDate, Score [0,400]
- X, '2020-01-15', 37
- X, '2020-02-03', 400
- X, '2020-02-07', 327
- X, '2020-05-29', 321
- X, '2020-08-01', 244
Table B: Interactions
- UserID, EventDate, EventType, Offer
- X, '2020-03-01', View, AA
- X, '2020-03-01', Click, AA
- X, '2020-04-07', View, BB
- X, '2020-04-28', View, FF
- X, '2020-06-03', View, FF
- X, '2020-06-03', Click, FF**
Based on the data provided, construct a SQL query that returns CTR% (sum(clicks) / sum(views)) by different score bins of 200 ('0-200', '201-400') Expected Output:
- score_bins, ctr
- ‘0-200’, X%
- ‘201-400’, X%