How to count users' unique activity dates across many tables?-CodePudding

I have a Table A, with username,min_date and 5 more Tables B/C/D/E/F (representing products) with username,date. Each entry in the the latter tables represents that said user was active on that day.

I want to get for each user, where A.min_date >= than B/C/D/E/F.date, a count of the unique dates they were active. For instance, when a user was active in many products for a given day that should count as 1. This is for SQL Server and I'm not sure what is the best approach. Appreciate any help or tips.

CodePudding user response：

select a.username, count(*)
from A a inner join (
    /* duplicate results will be removed by the union */
    select username, date from B union
    select username, date from C union
    select username, date from D union
    select username, date from E union
    select username, date from F
) u on u.username = a.username
where a.min_date >= u.date
group by username;

If the optimizer doesn't push the date filter down to the tables participating in the union then those could be moved down into the individual queries.