Querying the snowflake schema
Imagine that you didn't have the data warehouse set up. Instead, you'll have to run this query on the company's operational database, which means you'll have to rewrite the previous query with the following snowflake schema:
The tables in this schema have been loaded. Remember, our goal is to find the amount of money made from the novel genre in each state.
Questo esercizio fa parte del corso
Database Design
Istruzioni dell'esercizio
- Select
statefrom the appropriate table and the totalsales_amount. - Complete the two JOINS to get the
genre_id's. - Complete the three JOINS to get the
state_id's. - Conditionally select for books with the
genrenovel. - Group the results by state.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
-- Output each state and their total sales_amount
SELECT ___.___, ___(___)
FROM ___
-- Joins for genre
JOIN dim_book_sf on ___.___ = ___.___
JOIN dim_genre_sf on ___.___ = ___.___
-- Joins for state
JOIN ___ on ___.store_id = ___.store_id
JOIN ___ on ___.city_id = ___.city_id
JOIN ___ on ___.state_id = ___.state_id
-- Get all books with in the novel genre and group the results by state
WHERE
___.___ = '___'
GROUP BY
___.___;