CODEWITHSUNDEEP
mysqlsql
Emily Kuo
Emily Kuo

Reputation: 71

In SQL, how to get two different string values to do an aggregate function?

Write a query to get the click-through rate per app in 2019. The schema of the events table is app_id (integer), event_id (string: "impression", "click", and timestamp (datetime). My query is below, but not sure how to filter by impression or click to calculate the click-through rate.

SELECT app_id, COUNT(event_id = ‘click’ ) / COUNT (event_id = ‘impression’) as click_through_rate
FROM events 
WHERE  EXTRACT(YEAR from timestamp) = 2019
GROUP BY app_id
ORDER BY click_through_rate DESC

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

I think you want sum():

SELECT app_id, 
       SUM(event_id = ‘click’ ) / SUM(event_id = ‘impression’) as click_through_rate
FROM events 
WHERE  EXTRACT(YEAR from timestamp) = 2019
GROUP BY app_id
ORDER BY click_through_rate DESC;

COUNT() counts the number of non-NULL values for the expression. You want the sum where the expression is true -- hence SUM() instead of COUNT().

Upvotes: 1

Related Questions