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.

This is a part of the course

“Database Design”

View Course

Exercise instructions

Select state from the appropriate table and the total sales_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 genre novel.
Group the results by state.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- 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
    ___.___;

Edit and Run Code