How to come up with sum based on values in a column across multiple tables in SQL-CodePudding

I have 3 tables; store1, store2, store3. Within each table, I have the relevant columns manager_name and audit_date. The gist is that some managers are doing audits across several store locations. I want to write a query as a common table expression that gives the output of manager_name along with number of audits each one did from the last 90 days given that the manager has done more than 10 audits in those last 90 days. So the output should be manager name along with total number of audits done within the last 90 days if that manager has greater than 10 audits total amongst all 3 stores in that timeframe. I'm a confused on whether to do a UNION ALL or a JOIN, but here is what I have so far,

WITH total_audit AS
(SELECT s1.manager_name, s1.audit_date, s2.manager_name, s2.audit_date, s3.manager_name, s3.audit_date, count(manager_name)
FROM store1 as s1
INNER JOIN store2 as s2
ON s1.manager_name = s2.manager_name
INNER JOIN store3 as s3
ON s1.manager_name = s3.manager_name
HAVING COUNT(audit_date)>10
WHERE audit_date >= CURRENT_TIMESTAMP-90)

Could someone help me debug this? I know there's an issue with my common table expression as well as some of the syntax used.

CodePudding user response：

WITH
    all_audit_records AS (
        (
            SELECT
                manager_name AS manager_name,
                audit_date   AS audit_date
            FROM store1
        )
        UNION ALL
        (
            SELECT
                manager_name AS manager_name,
                audit_date   AS audit_date
            FROM store2
        )
        UNION ALL
        (
            SELECT
                manager_name AS manager_name,
                audit_date   AS audit_date
            FROM store3
        )
    ),
    those_audited_10_times_above AS (
        SELECT
            manager_name,
            COUNT(*) AS no_of_audits
        FROM all_audit_records
        WHERE
            audit_date >= CURRENT_TIMESTAMP - INTERVAL '90 day'
        GROUP BY manager_name
        HAVING COUNT(*) > 10
    )
SELECT
    *
FROM those_audited_10_times_above