Combine results of two queries, where the second relies on the first

I have one query which is returning

name(text), total, created_at

And another query which is returning the same thing

name(text), total, created_at

The second query usually is returning more rows, but I only need the rows where the query1.created_at = query2.created_at

Example:

Query 1 returns:

test, 10, 2015-03-06
test, 12, 2015-03-07

Query 2 returns:

newtest, 14, 2015-03-05
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

The expected output:

test, 10, 2015-03-06
test, 12, 2015-03-07
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

QUERY 1:

SELECT
  co.name
  sum(c.total)
  c.created_at
FROM c_report c
INNER JOIN contact co on co.id = c.contact_id
GROUP BY co.name, c.created_at

QUERY 2:

SELECT
  po.name
  sum(p.total)
  p.created_at
FROM p_report p
INNER JOIN person po on po.id = p.person_id
GROUP BY po.name, p.created_at

I would use a CTE for the first query and reuse it for the second query:

WITH cte AS (
   SELECT co.name, sum(c.total) AS total, c.created_at
   FROM   c_report c
   JOIN   contact  co ON co.id = c.contact_id
   GROUP  BY co.name, c.created_at
   )
SELECT * FROM cte
UNION ALL
SELECT po.name, sum(p.total), p.created_at
FROM   p_report p
JOIN   person   po ON po.id = p.person_id
WHERE  EXISTS (SELECT 1 FROM cte WHERE created_at = p.created_at)
GROUP  BY po.name, p.created_at;

Be sure to use UNION ALL, not UNION. You don't want to remove duplicates, and it's faster that way.

StackExchange Database Administrators Q#94661, answer score: 4