google-bigquerygoogle-cloudsql

Sniffed It

Ben Gorman

Ben Gorman

Life's a garden. Dig it.

Challenge

You operate a forum discussing dogs called Sniffed It 🐶

Your data consists posts

posts
| id | created_at              | title                            |
|---:|:------------------------|:---------------------------------|
|  1 | 2008-12-25 15:30:00 UTC | Chores Your Dog Can Do           |
|  2 | 2009-05-15 12:30:00 UTC | Teach Your Dog To Find Your Keys |

and comments

comments
| id | created_at              | post_id | content                                |
|---:|:------------------------|--------:|:---------------------------------------|
| 11 | 2008-12-25 15:35:00 UTC |       1 | This is ridiculous                     |
| 22 | 2009-05-15 12:39:00 UTC |       2 | I'm not going to soak my keys in gravy |
| 33 | 2008-12-25 15:48:00 UTC |       1 | Finally my dog is pulling his weight   |

Combine them into one table, such that comments are repsented as an array of structs, like this 👇

expected
| id | created_at              |                            title | comments.id | comments.created_at     | comments.content                       |
|---:|:------------------------|---------------------------------:|------------:|:------------------------|:---------------------------------------|
|  1 | 2008-12-25 15:30:00 UTC |           Chores Your Dog Can Do |          11 | 2008-12-25 15:35:00 UTC | This is ridiculous                     |
|    |                         |                                  |          33 | 2008-12-25 15:48:00 UTC | Finally my dog is pulling his weight   |
|  2 | 2009-05-15 12:30:00 UTC | Teach Your Dog To Find Your Keys |          22 | 2009-05-15 12:39:00 UTC | I'm not going to soak my keys in gravy |

Starter code

WITH
 
posts AS (
  SELECT 
    1 AS id, 
    TIMESTAMP("2008-12-25 15:30:00+00") AS created_at,
    "Chores Your Dog Can Do" AS title
 
  UNION ALL SELECT
    2 AS id, 
    TIMESTAMP("2009-05-15 12:30:00+00") AS created_at,
    "Teach Your Dog To Find Your Keys" AS title
),
 
comments AS (
  SELECT 
    11 AS id, 
    TIMESTAMP("2008-12-25 15:35:00+00") AS created_at,
    1 AS post_id, 
    "This is ridiculous" AS content, 
 
  UNION ALL SELECT
    22 AS id,
    TIMESTAMP("2009-05-15 12:39:00+00") AS created_at,
    2 AS post_id, 
    "I'm not going to soak my keys in gravy" AS content
 
  UNION ALL SELECT
    33 AS id, 
    TIMESTAMP("2008-12-25 15:48:00+00") AS created_at,
    1 AS post_id, 
    "Finally my dog is pulling his weight" AS content
)
 
-- Your code here
-- ...

Solution

This content is gated

Subscribe to one of the products below to gain access