LoslegenKostenlos loslegen

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.

Diese Übung ist Teil des Kurses

Introduction to Data Modeling in Snowflake

Kurs anzeigen

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

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);
Code bearbeiten und ausführen