Getting a unique count of events-CodePudding

I'm looking for some guidance as I'm struggling to create a script for the fiddle below which will output a high level table of results.

fiddle =

CodePudding user response：

You should group only by start_date and campaign_name and include DISTINCT and the CASE expressions inside COUNT():

select start_date, campaign_name,
       count(distinct case when status = 'Success' then userid end) as Unique_Sent,
       count(distinct case when status = 'opens' then userid end) as Unique_Opened,
       count(distinct case when status = 'clicked' then userid end) as Unique_Clicked,
       round(100 * count(distinct case when status = 'opens' then userid end) /
              count(distinct case when status = 'Success' then userid end)) as `% Opened`,
       round(100 * count(distinct case when status = 'clicked' then userid end) /
             count(distinct case when status = 'Success' then userid end)) as `% Clicked`
from Main
group by start_date, campaign_name;

See the