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

Question

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

Answer 1

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.

Answer 2

I presume you mean you want only those rows from the second query where the date of the row exists in the table of the first query. You could modify your second query so it checks the exitence of the row in the first table. Use UNION to combine the queries (UNION ALL if there could be duplicates and you want to preserve them). For example:

SELECT
  co.name
  sum(c.total)
  c.created_at
  FROM c_report c
  INNER JOIN c.contact co on co.id = c.contact_id
  GROUP BY co.name, c.created_at
UNION
SELECT
  pe.name
  sum(c.total)
  p.created_at
  FROM p_report p
  INNER JOIN p.person po on po.id = p.person_id
  WHERE EXISTS (SELECT 1 FROM c_report cc WHERE cc.created_at = p.created_at)
  GROUP BY po.name, p.created_at;

More info from the documetation:
9.22. Subquery Expressions - EXISTS
7.4. Combining Queries