How would you solve SQL problems involving joins, aggregates, window functions, and CTEs for a bookstore-style schema?
Use the provided bookstore schema to produce a monthly sales analysis. Include only months with sales and return the two highest-revenue books per genre and month.
genre, month, book_title, author_name, book_revenue, book_rank, genre_revenue, prior_month_genre_revenuegenre, month, book_rank, and book_title| Column | Type | Description |
|---|---|---|
| author_idPK | INT | Unique author identifier |
| author_name | VARCHAR(120) | Author's display name |
| Column | Type | Description |
|---|---|---|
| book_idPK | INT | Unique book identifier |
| title | VARCHAR(200) | Book title |
| genre | VARCHAR(60) | Book genre |
| author_id | INT | Referenced author, when known |
| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sale identifier |
| book_id | INT | Book sold |
| sale_date | DATE | Date of sale |
| quantity | INT | Number of copies sold |
| unit_price | DECIMAL(10,2) | Price per copy |