How to Calculate First Time City Visits Year Wise in SQL : SQL Problem Solving with CTEs
ฝัง
- เผยแพร่เมื่อ 7 ก.พ. 2025
- In this SQL problem-solving session, we tackle a fascinating real-world problem: finding the count of cities visited for the first time by each traveler, grouped by year. Using a dataset of travelers and their city visits, we demonstrate how to approach this challenge efficiently with SQL techniques such as Common Table Expressions (CTEs) and aggregate functions.
𝑻𝒉𝒆 𝒔𝒆𝒔𝒔𝒊𝒐𝒏 𝒄𝒐𝒗𝒆𝒓𝒔:
🔎 Grouping and aggregating data by year.
🔎 Identifying first-time city visits for travelers.
🔎 Using MIN, GROUP BY, and STRING_AGG to extract meaningful insights.
This video is ideal for data enthusiasts, SQL learners, and professionals dealing with travel or user behavior datasets, providing valuable tips for solving similar problems in SQL.
𝑻𝒂𝒃𝒍𝒆 𝒄𝒓𝒆𝒂𝒕𝒊𝒐𝒏 & 𝒊𝒏𝒔𝒆𝒓𝒕𝒊𝒐𝒏 𝒔𝒄𝒓𝒊𝒑𝒕:
CREATE TABLE travels (
traveller_id INT,
traveller_name VARCHAR(50),
city VARCHAR(50),
visit_date DATE
);
INSERT INTO travels (traveller_id, traveller_name, city, visit_date) VALUES
(1, 'Madhavi', 'Mumbai', '2020-01-05'),
(1, 'Madhavi', 'Delhi', '2021-02-14'),
(1, 'Madhavi', 'Mumbai', '2021-05-20'),
(1, 'Madhavi', 'Pune', '2021-03-15'),
(1, 'Madhavi', 'Pune', '2023-07-10'),
(2, 'Aditi', 'Bangalore', '2020-06-18'),
(2, 'Aditi', 'Lucknow', '2021-08-25'),
(2, 'Aditi', 'Lucknow', '2021-09-11'),
(2, 'Aditi', 'Jaipur', '2023-09-30'),
(3, 'Vikram', 'Delhi', '2021-11-03'),
(3, 'Vikram', 'Mumbai', '2022-02-05'),
(3, 'Vikram', 'Nashik', '2022-12-22'),
(3, 'Vikram', 'Pune', '2022-01-10'),
(3, 'Vikram', 'Hyderabad', '2022-06-18');
#SQL, #DataAnalysis, #TravelData, #SQLCTE, #DataEngineering, #SQLQueries, #FirstTimeVisits, #AdvancedSQL, #SQLTutorial, #LearnSQL
