Post Success By Interface
Medium
You are given the following tables:
post table:
post_user table
Write an SQL query that calculates the success rate (in percentage) of posts originating from various iPhone models.
Your output should have the following columns: interface, post_success (no. of successful posts), post_attempt (no. of posts), post_success_rate (Round to 2 decimal place). Order by descending success rate.
SELECT
p.interface,
SUM(p.is_successful_post) AS post_success,
COUNT(p.is_successful_post) AS post_attempt,
ROUND(SUM(p.is_successful_post) * 100.0 / COUNT(p.is_successful_post), 2) AS post_success_rate
FROM post AS p
WHERE p.interface LIKE 'Iphone%'
GROUP BY 1
ORDER BY post_success_rate DESC;
Select interface, Count(case when is_successful_post then 1 end) as post_success, Count(_) as post_attempt, ROUND((COUNT(CASE WHEN is_successful_post THEN 1 END) * 100 / COUNT(_)), 2) AS post_success_rate from post where interface like 'Iphone%' group by 1 order by post_success_rate desc