Naveen SQL-CASE-WHEN STATEMENT

 SQL :- Use cases. When statement task....


First of all we created the Source DE : Naveen_CaseWhen_Source_DE

Target DE Created Named : Naveen_CaseWhen_Target_DE


TASK : 1 

1. Age Group Segmentation:

Query :

SELECT FirstName,
CASE WHEN Age < 18 THEN 'Minor'
           WHEN Age between 18 and 40 THEN 'Major'
           ELSE 'Senior'
END AS AgeGroup
FROM Naveen_CaseWhen_Source_DE



Result fetched with Column AgeGroup stating : Condition per Age group




Task 2

2. Engagement Level :

Query :
SELECT  FirstName,
CASE WHEN OpenCount > 10 THEN 'High'
           WHEN OpenCount between 5 and 10 THEN 'Medium'
           ELSE 'Low'
END AS Engagement
FROM Naveen_CaseWhen_Source_DE 



Result fetched with Column Engagement stating : OpenCount numbers Level



Task 3 : 

3. Greeting :-

Query :

SELECT FirstName,
CASE WHEN Country = 'India' THEN 'Namaste'
          WHEN Country = 'USA' THEN 'Hello'
          ELSE 'Hi'
END AS [Greeting]
FROM Naveen_CaseWhen_Source_DE


Result fetched with Column Greeting stating : Greetings as per Country





Task 4 

Status Mapping:
SELECT FirstName,
CASE
        WHEN Status = 'A' THEN 'Active'
        WHEN Status = 'I' THEN 'Inactive'
        ELSE 'Unknown'
END AS StatusLabel
FROM Naveen_CaseWhen_Source_DE 

Query : 

Result fetched with Column StatusLabel stating : Active or Inactive Status






Task 5:
Purchase Segmentation

Query :

SELECT FirstName,
CASE
        WHEN PurchaseAmount > 2000 THEN 'Premium'
        WHEN PurchaseAmount BETWEEN 500 AND 2000 THEN 'Standard'
        ELSE 'Low Value'
END AS CustomerType
FROM Naveen_CaseWhen_Source_DE 

Query:- 

Result fetched with Column StatusLabel stating : Active or Inactive Status




Task 6:

Re-engagement : 

Query :

SELECT FirstName,
CASE 
           WHEN OpenCount = 0 THEN 'Re-engage'
           ELSE 'Active User'
END AS CampaignType
FROM Naveen_CaseWhen_Source_DE 

Query:- 


Result fetched::: with Column CampaignType 





Task 7 :   Youth Campaign :

Query :

SELECT FirstName,
CASE WHEN Age < 25 THEN 'Youth Campaign'
           ELSE 'General Campaign'
END AS Campaign
FROM Naveen_CaseWhen_Source_DE


Result : Column with Name - Campaign Returned with records



Task 8. VIP Segment

Query:

SELECT FirstName,
CASE WHEN PurchaseAmount > 1000 AND OpenCount > 10 THEN 'VIP'
           ELSE 'Normal'
END AS Segment
FROM Naveen_CaseWhen_Source_DE



Result : Column with Name - Segment Returned with records




Task 9 :  Email Strategy
Query:

SELECT FirstName,
CASE
        WHEN OpenCount > 10 THEN 'Daily Emails'
        WHEN OpenCount BETWEEN 5 AND 10 THEN 'Weekly Emails'
        ELSE 'Monthly Emails'
END AS EmailStrategy
FROM Naveen_CaseWhen_Source_DE 


Result : Column with Name - EmailStrategy Returned with records



TASK : 10

Risk Identification

Query:

SELECT FirstName,
CASE
WHEN OpenCount < 3 AND PurchaseAmount < 500 THEN 'At Risk'
ELSE 'Safe'
END AS RiskLevel
FROM Naveen_CaseWhen_Source_DE 

Query:- 


Result : Column with Name - RiskLevel Returned with records





  

Comments

Post a Comment

Popular posts from this blog

Data filter

Image
Filtered Data Extensions: - https://www.loom.com/share/d9b7ca63c2af4dd5b0137e557fcd306a Generated by applying filters to existing data extensions; they update dynamically based on predefined criteria. USE CASES 1. High-Value Customers Segmentation Scenario : A retail company wants to target customers who have spent more than $500 in the last six months. Solution : Use a filtered data extension to segment customers from a standard DE based on purchase amount and date criteria. Field Name Data Type Length Required Primary Key CustomerID Text 50 Yes Yes FullName Text 100 Yes No EmailAddress EmailAddress N/A Yes No PurchaseAmount Decimal N/A Yes No LastPurchaseDate Date N/A Yes No CustomerID FullName EmailAddress PurchaseAmount LastPurchaseDate CustomerID FullName EmailAddress PurchaseAmount LastPurchaseDate C001 John Doe john.doe@example.com 550.00 2024-12-15 C003 Alice Johnson alice.johnson@example.com 750.00 2024-11-20 Initially created a table for 4 records. After applying the filter...
Read more