Subquery mastery
You are the data modeler of a business that manages hotel chain data. The business team has the task of examining customer data to inform retention strategies. The marketing department needs insights into high-income customers' activity status and engagement with the hotel's services. Your goal is to use the existing customer data to identify active and churned customers with a high estimated salary and to analyze their average age and tenure. To achieve this goal, you can use this pre-built common table expression CustomerStatus.
Latihan ini adalah bagian dari kursus
Introduction to Data Modeling in Snowflake
Latihan interaktif praktis
Cobalah latihan ini dengan menyelesaikan kode contoh berikut.
WITH customer_status AS (
SELECT c.customerid,
c.age,
c.tenure,
CASE
WHEN ch.customerid IS NOT NULL THEN 'Churned'
ELSE 'Active'
END AS status
FROM customers AS c
LEFT JOIN churn AS ch
ON c.customerid = ch.customerid
GROUP BY c.customerid, c.age, c.tenure, status
)
-- Extract attribute from CTE
___
FROM customer_status
-- Filter results
___ customerid IN (___ customerid
___
___ estimatedsalary > 175000);