带动销量的专辑
今天主管来到您的工位，提前透露即将上线的节日促销活动：主打 "Greatest Hits"（精选集）专辑。为确保折扣投放到最合适的专辑上，她想知道哪些 "Greatest Hits" 专辑带动的销量最高。为此，您将综合运用本章学到的所有技能来完成分析！
本练习是课程的一部分
Snowflake 中的数据操作
练习说明
- 定义一个名为
album_map的 CTE。 - 编写一个
CASE语句：当专辑标题中包含greatest时返回TRUE，否则返回FALSE，并将结果命名为is_greatest_hits。 - 更新
trimmed_invoicelinesCTE，将invoice和tracks表分别基于invoice_id和track_id与invoicelines进行LEFT JOIN。 - 使用子查询仅返回 "Greatest Hits" 专辑。
交互式实操练习
通过完成这段示例代码来试试这个练习。
-- Define an album_map CTE to combine albums and artists
___ (
SELECT
album.album_id, album.title AS album_name, artist.name AS artist_name,
-- Determine if an album is a "Greatest Hits" album
___
___ album_name ILIKE '%greatest%' ___ TRUE
ELSE FALSE
___ AS ___
FROM store.album
JOIN store.artist ON album.artist_id = artist.artist_id
), trimmed_invoicelines (
SELECT
invoiceline.invoice_id, track.album_id, invoice.total
FROM store.invoiceline
LEFT JOIN store.invoice ON invoiceline.invoice_id = invoice.invoice_id
LEFT JOIN store.track ON invoiceline.track_id = track.track_id
)
SELECT
album_map.album_name,
album_map.artist_name,
SUM(ti.total) AS total_sales_driven
FROM trimmed_invoicelines AS ti
JOIN album_map ON ti.album_id = album_map.album_id
-- Use a subquery to only "Greatest Hits" records
___ ti.___ ___ (SELECT album_id FROM album_map WHERE is_greatest_hits)
GROUP BY album_map.album_name, album_map.artist_name, is_greatest_hits
ORDER BY total_sales_driven DESC;