Average of movies watched each day query

[mokje5]

Hey, I have the following tables:

------Customer------
customer_email_address (PK)
subscription_start_date
subscription_end_date
-----------------------

-----Watchhistory-----
customer_email_address (FK)
movie_id
price
--------------------------

Now I would like to make a query that selects the customer_mail_address from Customer and the average of movies watched per day between the subscription_start_date and the current time (so right now).
A condition is to show only the records of customers that have an average of 2 watched movies per day.

So the query will look something like this:

SELECT Customer.customer_email_address, (some calculation of movies watched per day here) AS watchedPerDay 
FROM Customer INNER JOIN Watchhistory ON Customer.customer_email_address = Watchhistory.customer_email_address
GROUP BY Customer.customer_email_address, (calculation here again?)
HAVING (2 or more watched movies per day calculation)

Anyone can help?

[ranman256]

its 2 queries, Q1 get avg
select customer , AVg(movieID) as AvgRent from table where rentDate between subscripSTart and subscripEnd

then Q2 uses Q1
select customer ,AvgRent from Q1 where AvgRent = 2

[mokje5]

I think it's 2 queries too, but your solution does unfortunatly not really help me, sorry

[orange]

I think the calculations would be along this line:

A: Number of Days between Today and SubscriptionStartDate

B: Number of Movies for this Customer

Avg number of movies watched per day = B/A

[mokje5]

Alright, my friend came up with this solution:

SELECT temp.customer_mail_address, Round((Avg(counts)/DateDiff("d",temp.subscription_start,Now())),2) AS average_moviesFROM (SELECT DATEDIFF("d",Customer.subscription_start,NOW()) AS time_overlaps, Watchhistory.customer_mail_address, count(*) AS counts, Customer.subscription_start FROM Watchhistory INNER JOIN Customer ON Watchhistory.customer_mail_address =  Customer.customer_mail_address GROUP BY Watchhistory.customer_mail_address, Customer.[subscription_start])  AS temp
GROUP BY temp.customer_mail_address, DateDiff("d",temp.subscription_start,Now())
HAVING (((Avg([counts])/DateDiff("d",[temp].[subscription_start],Now()))>=2));

This query works, but I can't really understand whats going on here, and neither can my friend :P Maybe someone has an easier variant to this query, or someone can explain?