Click here to Skip to main content
15,435,848 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Given 2 tables, i want to find the % of users who saw fresh pins on a given day. Fresh is defined as a pin that has at least 2 impressions with impression_type =1 within 7 days of the created_date.

Table 1: pins

pin_id	created_date
ABC	01/01/2017

Table 2: events

user_id	pin_id	date_impression	impression_type	count
JohnSmith	ABC	01/01/2018	1	1


What I have tried:

SQL

SELECT round(count(case when "created_date" = xyz then 1 else NULL end) / count(*)*100,2)
from
(
SELECT count(*)
FROM events e
where "impression_type" = 1
AND count >=2)
Posted
Comments
CHill60 25-Jul-22 12:32pm    
More than one line of sample data would be appropriate, and you should include some expected results

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900