WITH stock_t AS
(
SELECT YEAR("date") AS "year", "open" AS "open_price"
FROM (SELECT "date", "open",
ROW_NUMBER() OVER (partition by EXTRACT(YEAR FROM "date"), EXTRACT(MONTH FROM "date") ORDER BY EXTRACT(DAY FROM "date")) AS seq
FROM @kaggle.rprkh15_sp500_stock_prices.mmm
) t
WHERE seq = 1 AND MONTH("date")=1 AND "open" != 0
ORDER BY 1
),
name_t AS
(
SELECT "year", SUM("count") AS "babies"
FROM @kaggle.robikscube_us_baby_name_popularity.names
WHERE "name" = 'Rose'
GROUP BY "year"
)
SELECT stock_t."year", stock_t."open_price"*15 AS "scaled 3M stock price", name_t."babies" AS "babies named rose"
FROM stock_t, name_t
WHERE stock_t."year" = name_t."year" AND stock_t."year" > 2000
ORDER BY 1