Question: Recommendation System Efficiency
"Given recommendation and watch logs, calculate the ratio of recommended content that was actually watched by users within 7 days of the recommendation."

You have two tables:

1. recommendations
   • user_id
   • content_id
   • recommendation_time (timestamp when content was recommended)
2. watch_history
   • user_id
   • content_id
   • watch_time (timestamp when the user watched the content)

You need to:

1. Find out, for each recommended content:
   • Whether the user watched the recommended content within 7 days of the recommendation.
2. Calculate:
   • Total number of recommendations.
   • Total number of recommendations actually watched within 7 days.
   • Ratio = (Watched within 7 days) / (Total recommendations)

Sample Data:

recommendations

watch_history

Step-by-Step Explanation:

Step 1: Match Recommendations and Watches

Join recommendations and watch_history on user_id and content_id.

Only keep the cases where:

• watch_time is after recommendation_time
• AND watch_time is within 7 days of recommendation_time.

Step 2: Count

• Total recommendations: Count all rows from recommendations.
• Watched recommendations: Count only successful matches from the join.

Step 3: Calculate Ratio

• Ratio = (Watched within 7 days) / (Total recommendations)

SQL Query:

WITH watched_within_7_days AS (
  SELECT
    r.user_id,
    r.content_id
  FROM
    recommendations r
  JOIN
    watch_history w
  ON
    r.user_id = w.user_id
    AND r.content_id = w.content_id
  WHERE
    w.watch_time >= r.recommendation_time
    AND w.watch_time <= DATE_ADD(r.recommendation_time, INTERVAL 7 DAY)
)

SELECT
  (SELECT COUNT(*) FROM watched_within_7_days) AS watched_count,
  (SELECT COUNT(*) FROM recommendations) AS total_recommendations,
  ROUND(
    (SELECT COUNT(*) FROM watched_within_7_days) * 100.0 / (SELECT COUNT(*) FROM recommendations),
    2
  ) AS recommendation_efficiency_percentage
;

Explanation of SQL:

• watched_within_7_days CTE:
  • Filters recommendations that were watched within 7 days.
• First subquery: counts how many recommendations were successfully watched.
• Second subquery: counts total number of recommendations.
• Finally calculates the efficiency percentage and rounds it to 2 decimal places.

Expected Output:

Explanation:

• User 1 watched 101 within 7 days.
• User 2 watched 103 within 7 days.
• User 1 watched 102, but after 18 days → not counted.

Important Points:

• The watch_time must be after the recommendation_time and within 7 days.
• If a user never watches a recommendation or watches after 7 days, it doesn't count.
• Always divide watched_count by total_recommendations for efficiency calculation.
• Multiply by 100.0 to get a percentage if needed.