SQL request (A/B opened and clicked)

[upi]

Hello,

Tell me plz your SQL request for A/B opened and clicked (I attached the picture)

I want to make my custom report with (Name of broadcast; sent; open rate; click rate) for them all broadcasts a not campaigns.
It's only your SQL request then I will do everything myself.

 

[marfan]

Hi @upi
Here is the query that will give you sent,open and click for all your campaigns.

select c.name as name, c.emails_sent as total_sent,  count(e.scheduled_id) as total_opened , SUM(e.is_clicked) as total_clicked
from campaign_schedules as c
left join  email_opened as e
ON c.id = e.scheduled_id
group by c.id

 

[upi]

Hi @marfan. Thx
I know how to make this request.
But how to make a multi request with a lot of broadcasts in A/B?
Since the table with sent emails located here campaign_schedule_logs and open rate and click rate here email_opened. How to combine it to multi request?

select c.name as name, count(csl.campaign_id) as total_sent, count(e.is_clicked) / count(csl.campaign_id) * 100 as total_opened , SUM(e.is_clicked) / count(csl.campaign_id) * 100 as total_clicked
from campaigns as c
left join campaign_schedule_logs as csl
ON c.id = csl.campaign_id
left join  email_opened as e
ON c.id = e.broadcast_id
WHERE c.user_id = 2
group by e.broadcast_id