Querying data view (Open) and grouping by date in SFMC

Question

I am trying to get the number of opens for each day (Example: April 15, April 16, April 17 and so on....)

Date	Opens
Apr 15	17500
Apr 16	5000
Apr 17	200

I am using the below query, but not getting required results.

select distinct count (o.SubscriberKey), o.EventDate as date
from [_Open] o
inner join [Emails_Sent_on_April15] s
on o.JOBID= s.JOBID   and   s.BATCHID=o.BATCHID
where o.isunique = 1
group by cast (o.EventDate as date )

Could anyone help me know, how to modify the above query to get the results like in the table.

The problem is o.EventDate in select statement also has time

Answer 1

With a shift, I was able to get this to work correctly. If you have a high volume, this may certainly time out, but if you place a date range in it, should work:

select
count(o.SubscriberKey) as cnt, 
CONVERT(date,o.EventDate) as dte
from [_Open] o
WHERE EXISTS (SELECT se.SubscriberKey FROM [_Sent] se WHERE se.SubscriberKey = o.SubscriberKey)
and o.isunique = 1
group by CONVERT(date,o.EventDate)

And if you want it also by JobID to differentiate send jobs per day, you can do:

select
count(o.SubscriberKey) as cnt, 
CONVERT(date,o.EventDate) as dte,
CONVERT(nvarchar(max),o.JobID) as JobID
from [_Open] o
WHERE EXISTS (SELECT se.SubscriberKey FROM [_Sent] se WHERE se.SubscriberKey = o.SubscriberKey)
and o.isunique = 1
group by CONVERT(date,o.EventDate), CONVERT(nvarchar(max),o.JobID)

Just replace the _Sent DV (in my EXISTS statement) with your DE.