Vet Visits¶

You run a vet clinic

You have a table of pets

pets

| id | name         |
|---:|:-------------|
|  1 | Tootsie      |
|  2 | Sparky       |
|  3 | Fishbait     |
|  4 | Sgt. Slobber |

and a table of visits

visits

| pet_id | visit_date |
|-------:|:-----------|
|      2 | 2022-01-05 |
|      1 | 2022-01-25 |
|      4 | 2022-03-13 |
|      4 | 2022-05-22 |
|      1 | 2022-07-29 |
|      2 | 2022-07-04 |
|      1 | 2022-10-11 |
|      1 | 2022-10-28 |

Combine them into one table, such that visit_dates is an array column inside the pets table.

expected

| id | name         | visit_dates                                      |
|---:|:-------------|:-------------------------------------------------|
|  1 | Tootsie      | [2022-01-25, 2022-07-29, 2022-10-11, 2022-10-28] |
|  2 | Sparky       | [2022-01-05, 2022-07-04]                         |
|  3 | Fishbait     | []                                               |
|  4 | Sgt. Slobber | [2022-03-13, 2022-05-22]                         |

Starter code¶

WITH 

pets AS (
  SELECT 1 as id, "Tootsie" as name,
  UNION ALL SELECT 2 as id, "Sparky" as name
  UNION ALL SELECT 3 as id, "Fishbait" as name,
  UNION ALL SELECT 4 as id, "Sgt. Slobber" as name
),

visits as (
  SELECT 2 as pet_id, DATE(2022, 1, 5) as visit_date,
  UNION ALL SELECT 1 as pet_id, DATE(2022, 1, 25) as visit_date,
  UNION ALL SELECT 4 as pet_id, DATE(2022, 3, 13) as visit_date,
  UNION ALL SELECT 4 as pet_id, DATE(2022, 5, 22) as visit_date,
  UNION ALL SELECT 1 as pet_id, DATE(2022, 7, 29) as visit_date,
  UNION ALL SELECT 2 as pet_id, DATE(2022, 7, 4) as visit_date,
  UNION ALL SELECT 1 as pet_id, DATE(2022, 10, 11) as visit_date,
  UNION ALL SELECT 1 as pet_id, DATE(2022, 10, 28) as visit_date,
)

-- Your code here
-- ...