CommencerCommencer gratuitement

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.

Cet exercice fait partie du cours

Introduction to Data Modeling in Snowflake

Afficher le cours

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

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);
Modifier et exécuter le code