Querying the star schema
The novel genre hasn't been selling as well as your company predicted. To help remedy this, you've been tasked to run some analytics on the novel genre to find which areas the Sales team should target. To begin, you want to look at the total amount of sales made in each state from books in the novel genre.
Luckily, you've just finished setting up a data warehouse with the following star schema:
The tables from this schema have been loaded. Note that you should not use aliases in FROM and JOIN statements.
Questo esercizio fa parte del corso
Database Design
Istruzioni dell'esercizio
- Select
statefrom the appropriate table and the totalsales_amount. - Complete the JOIN on
book_id. - Complete the JOIN to connect the
dim_store_startable - 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 ___
-- Join to get book information
JOIN ___ ON ___.___ = ___.___
-- Join to get store information
JOIN ___ ON ___.___ = ___.___
-- Get all books with in the novel genre
WHERE
___.___ = 'novel'
-- Group results by state
GROUP BY
___.___;