Monthly Post Success Analysis
Easy
You are given the following tables:
post table:
post_user table
Write a SQL query that shows the total amount of successful post per user type in the current month (Assume current month is Nov 2023)
Your output should include the following columns: user_type, post_success (no. of successful posts), post_attempt (no. of posts), post_success_rate (range: 0.00 - 1.00). Order by descending success rate
SELECT
pu.user_type,
SUM(p.is_successful_post) AS post_success,
COUNT(p.is_successful_post) AS post_attempt,
ROUND(SUM(p.is_successful_post) * 1.0 / COUNT(p.is_successful_post), 2) AS post_success_rate
FROM post AS p
JOIN post_user AS pu ON p.user_id = pu.user_id
WHERE p.post_date BETWEEN '2023-11-01' AND '2023-11-30'
GROUP BY pu.user_type
ORDER BY post_success_rate DESC;
WITH filtered_posts AS ( SELECT p.user_id, p.is_successful_post FROM post p WHERE p.post_date >= '2023-11-01' AND p.post_date < '2023-12-01' ), post_summary AS ( SELECT pu.user_type, COUNT(*) AS post_attempt, SUM(CASE WHEN fp.is_successful_post = 1 THEN 1 ELSE 0 END) AS post_success FROM filtered_posts fp JOIN post_user pu ON fp.user_id = pu.user_id GROUP BY pu.user_type ) SELECT user_type, post_success, post_attempt, CAST(post_success AS FLOAT) / post_attempt AS post_success_rate FROM post_summary ORDER BY post_success_rate DESC;