How to count number of distinct days from one table using two dates (for range) from another table for each row

google-bigquery

So I have one datatable with three columns: userid, mindate, maxdate.
I have another datatable that contains all the login logs for each user.

I need to make a query so that I can sum all the distinct login days from one datatable between the mindate and maxdate from another datatable for each user.

The code I can write until:

SELECT a.*, b.logins FROM 'usertable' as a   
LEFT JOIN (SELECT user_id, COUNT(DISTINCT(loginday)) from 'logintable`    
GROUP BY user_id) as b   
on a.userid = b.user_id;

I just need help adding a 'where' statement that can query from b like:
WHERE loginday between a.mindate and a.maxdate.

Best Answer

SELECT a.*, COUNT(DISTINCT b.loginday)
FROM usertable a
JOIN logintable b 
    ON b.loginday BETWEEN a.mindate AND a.maxdate
GROUP BY a.userid /* , a.mindate, a.maxdate, ... */