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%

Similar Questions