Life's a garden. Dig it.
Challenge¶
You run a vet clinic 🐶
You have a table of pets
| id | name |
|---:|:-------------|
| 1 | Tootsie |
| 2 | Sparky |
| 3 | Fishbait |
| 4 | Sgt. Slobber |and a table of 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.
| 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
-- ...Solution¶
This content is gated
Subscribe to one of the products below to gain access
