PostgreSQL - Querying Second Table with Multiple Results I am working in PostgreSQL 9.6.6 on a query that involves the use of a second table to return additional data points. The table - "events" - is associated with many entries from the second - "activities" - via a foreign key.

Events

| id | name   |
|----|--------|
| 1  | event1 |
| 2  | event2 |
| 3  | event3 |


Activities

| id | name      | startDate              | endDate                | status    | eventId |
|----|-----------|------------------------|------------------------|-----------|---------|
| 1  | activity1 | 2018-08-27 05:00:00+00 | 2018-08-28 05:00:00+00 | Draft     | 1       |
| 2  | activity2 | 2018-09-27 05:00:00+00 | 2018-09-27 10:00:00+00 | Submitted | 1       |
| 3  | activity3 | 2018-08-25 05:00:00+00 | 2018-08-27 10:00:00+00 | Draft     | 1       |
| 4  | activity4 | 2018-08-21 05:00:00+00 | 2018-08-24 05:00:00+00 | Approved  | 2       |
| 5  | activity5 | 2018-09-27 05:00:00+00 | 2018-09-29 05:00:00+00 | Draft     | 2       |
| 6  | activity6 | 2018-10-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Approved  | 3       |
| 7  | activity7 | 2018-08-27 05:00:00+00 | 2018-08-27 10:00:00+00 | Approved  | 3       |


The data points being collected via the event are a startDate, endDate &amp; status based on the associated activities.


startDate => The least startDate of all associated activities.
endDate => The greatest endDate of all associated activities.
status => Status equals "Pending" unless all associated activities
have a status of "Approved" (then status equals "Complete").


An example returned query would look like:

| id | name   | startDate              | endDate                | status    |
|----|--------|------------------------|------------------------|-----------|
| 1  | event1 | 2018-08-25 05:00:00+00 | 2018-09-27 10:00:00+00 | Pending   |
| 2  | event2 | 2018-08-21 05:00:00+00 | 2018-09-29 05:00:00+00 | Pending   |
| 3  | event3 | 2018-08-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Completed |


I have formulated the following with multiple subqueries, but the performance is terrible.

Any suggestions as to how can I improve this to limit (or even remove) the use of subqueries?

SELECT DISTINCT ON("startDate", "event"."name") 1, "event"."id", "event"."name", (
  SELECT "activities"."startDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."startDate" ASC
  LIMIT 1
) AS "startDate", (
  SELECT "activities"."endDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."endDate" DESC
  LIMIT 1
) AS "endDate", (
  SELECT DISTINCT ON("event"."id")
    CASE "activities"."status"
    WHEN 'Draft' THEN 'Pending'
    WHEN 'Submitted' THEN 'Pending'
    ELSE 'Complete'
    END
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."status" DESC
) AS "status"
FROM "events" AS "event"
ORDER BY "startDate" DESC, "event"."name" ASC
LIMIT 20
OFFSET 0;
Test:

SELECT e.name, 
       MIN(a.startDate) startDate, 
       MAX(a.endDate) endDate, 
       CASE WHEN SUM(a.Status != 'Approved')=0 
              THEN 'Completed' 
              ELSE 'Pending' 
         END status
FROM Events e
JOIN Activities a ON e.id = a.eventId
GROUP BY e.name


If fail replace SUM(a.Status != 'Approved') with SUM(CASE WHEN a.Status != 'Approved' THEN 1 ELSE 0 END).