- 43
- 5 345
Code SQL
India
Приєднався 26 вер 2020
Welcome to @codesql! This channel is your go-to resource for mastering SQL, whether you're a beginner or looking to enhance your skills. As a Senior Statistical Programmer with experience at Parexel and Fortrea, I specialize in clinical data analysis and am excited to share my knowledge with you.
Here, you'll find step-by-step tutorials that break down SQL concepts into manageable parts, making learning easy and enjoyable. We’ll cover real-life examples from the clinical research field, showcasing how SQL is used to solve actual data problems. Expect tips and tricks to enhance your efficiency, as well as best practices for data management and analysis.
Join our community of learners, ask questions, and engage in discussions. Whether you're starting a new career or looking to improve your skills, @codesql is here to support you. Subscribe and hit the notification bell to stay updated on our latest content. Let’s unlock the power of SQL together!
Here, you'll find step-by-step tutorials that break down SQL concepts into manageable parts, making learning easy and enjoyable. We’ll cover real-life examples from the clinical research field, showcasing how SQL is used to solve actual data problems. Expect tips and tricks to enhance your efficiency, as well as best practices for data management and analysis.
Join our community of learners, ask questions, and engage in discussions. Whether you're starting a new career or looking to improve your skills, @codesql is here to support you. Subscribe and hit the notification bell to stay updated on our latest content. Let’s unlock the power of SQL together!
SQL - Analysing Customer | Identifying New and Repeat Customers
- Create the CityPopulation table
CREATE TABLE CityPopulation (
id INT PRIMARY KEY,
state VARCHAR(50),
city VARCHAR(50),
population INT
);
-- Insert sample data into the CityPopulation table
INSERT INTO CityPopulation (id, state, city, population) VALUES
(1, 'Maharashtra', 'Mumbai', 1000),
(2, 'Maharashtra', 'Pune', 500),
(3, 'Maharashtra', 'Nagpur', 400),
(4, 'Punjab', 'Amritsar', 800),
(5, 'Punjab', 'Ludhiana', 350),
(6, 'Punjab', 'Patiala', 200),
(7, 'TamilNadu', 'Chennai', 700),
(8, 'TamilNadu', 'Vellore', 400);
Select * from CityPopulation;
In this problem, we aim to analyse a dataset of customer orders to distinguish between new and repeat customers based on their purchasing behavior.
CREATE TABLE CityPopulation (
id INT PRIMARY KEY,
state VARCHAR(50),
city VARCHAR(50),
population INT
);
-- Insert sample data into the CityPopulation table
INSERT INTO CityPopulation (id, state, city, population) VALUES
(1, 'Maharashtra', 'Mumbai', 1000),
(2, 'Maharashtra', 'Pune', 500),
(3, 'Maharashtra', 'Nagpur', 400),
(4, 'Punjab', 'Amritsar', 800),
(5, 'Punjab', 'Ludhiana', 350),
(6, 'Punjab', 'Patiala', 200),
(7, 'TamilNadu', 'Chennai', 700),
(8, 'TamilNadu', 'Vellore', 400);
Select * from CityPopulation;
In this problem, we aim to analyse a dataset of customer orders to distinguish between new and repeat customers based on their purchasing behavior.
Переглядів: 8
Відео
SQL | Finding Call Duration Made to Each Person | Interview Problem
Переглядів 815 годин тому
Drop existing tables if they exist DROP TABLE IF EXISTS CallStartTimes; DROP TABLE IF EXISTS CallEndTimes; Step 1: Create the CallStartTimes table CREATE TABLE CallStartTimes ( Contact VARCHAR(50), StartTime DATETIME ); Step 2: Create the CallEndTimes table CREATE TABLE CallEndTimes ( Contact VARCHAR(50), EndTime DATETIME ); Step 3: Insert data into the CallStartTimes table INSERT INTO CallStar...
SQL Joins | Interview Question
Переглядів 1415 годин тому
In this video, we dive deep into the concept of joins in SQL and explore how they affect the number of rows returned in query results. 1. Inner Join 2. Left Join 3. Right Join 4. Full Outer Join
Complex SQL Query | Calculating Team Statistics | #sql
Переглядів 724 години тому
DROP TABLE CricketMatchResults; Create the CricketMatchResults table CREATE TABLE CricketMatchResults ( Team1 VARCHAR(50), Team2 VARCHAR(50), Winner VARCHAR(50) ); Insert data into the CricketMatchResults table INSERT INTO CricketMatchResults (Team1, Team2, Winner) VALUES ('India', 'Australia', 'India'), ('Australia', 'England', 'England'), ('South Africa', 'New Zealand', 'New Zealand'), ('Engl...
RANK, DENSE_RANK, ROW_NUMBER SQL Analytical Functions Simplified #dataanalysis
Переглядів 244 години тому
Welcome to my channel! Here, we simplify complex SQL concepts, focusing on analytical functions like RANK, DENSE_RANK, and ROW_NUMBER. Whether you're a beginner or an experienced data analyst, our videos break down these powerful SQL functions to help you understand how to rank and organize your data effectively.
SQL | Find Each Flight's Source and Destination | Capgemini | #DataEngineering | #PowerBI | #sql
Переглядів 2234 години тому
Problem: ua-cam.com/video/NuxMSyB07Ac/v-deo.html Lead Lag video: ua-cam.com/video/PfAlQstJ26Y/v-deo.htmlsi=S6XnoN3Qx2RF4Nw2 DROP TABLE flight_info; CREATE TABLE flight_info ( id INT, source VARCHAR(50), destination VARCHAR(50) ); Step 2: Insert data into the flight_info table INSERT INTO flight_info (id, source, destination) VALUES (1, 'Delhi', 'Kolkata'), (2, 'Kolkata', 'Banglore'), (3, 'Mumba...
SQL | From Transactions to Trends | #dataanalyst #sql
Переглядів 164 години тому
Step 1: Create the Results table CREATE TABLE trns ( id INT PRIMARY KEY, country VARCHAR(50), state VARCHAR(50), amount DECIMAL(10, 2), frans_date DATE ); Step 2: Insert data into the Results table INSERT INTO trns (id, country, state, amount, frans_date) VALUES (11, 'US', 'approved', 1000, '2023-12-18'), (2, 'US', 'declined', 2000, '2023-12-19'), (3, 'US', 'approved', 2000, '2024-01-01'), (4, ...
SQL | Lead and Lag Function
Переглядів 319 годин тому
The LEAD and LAG functions in SQL are powerful window functions that allow users to access data from subsequent or preceding rows within the same result set. The LEAD function retrieves values from a specified number of rows ahead, while the LAG function fetches values from a specified number of rows behind. These functions are particularly useful for analyzing trends, calculating differences b...
SQL | Calculate Working Days Excluding Holidays | #datascience #sas
Переглядів 3789 годин тому
Step 1: Create the ticket table CREATE TABLE ticket ( ticket_id INT PRIMARY KEY, issue_date DATE, resolve_date DATE ); Step 2: Insert data into the ticket table INSERT INTO ticket (ticket_id, issue_date, resolve_date) VALUES (1, '2024-12-18', '2025-01-07'), (2, '2024-12-20', '2025-01-10'), (3, '2024-12-22', '2025-01-11'), (4, '2025-01-02', '2025-01-13'); Step 3: Create the holiday table CREATE ...
#sql | PIVOTAL table format | Interview Problem | Exl Services #dataanalyst #dataengineering
Переглядів 1719 годин тому
Description: In this SQL program, we will demonstrate how to convert employee salary data into a pivot table format, where each salary component (salary, bonus, hike percentage) becomes a separate column. We will then show how to convert this pivot table back to the original data format. This process is useful for data analysis and reporting, allowing for easier interpretation of salary compone...
SQL | Names of Managers who manage more than 4 employees | #sql #data
Переглядів 4609 годин тому
Step 1: Create the Employee table CREATE TABLE Employee ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), manager_id INT NULL ); Step 2: Insert data into the Employee table INSERT INTO Employee (id, name, department, manager_id) VALUES (1, 'John', 'HR', NULL), (2, 'Bob', 'HR', 1), (3, 'Olivia', 'HR', 1), (4, 'Emma', 'Finance', NULL), (5, 'Sophia', 'HR', 1), (6, 'Mason', 'Finance',...
SQL | Find each flight's source and destination | Capgemini | #dataengineering | #powerbi | #sql
Переглядів 59912 годин тому
DROP TABLE flight_info; CREATE TABLE flight_info ( id INT, source VARCHAR(50), destination VARCHAR(50) ); Step 2: Insert data into the flight_info table INSERT INTO flight_info (id, source, destination) VALUES (1, 'Delhi', 'Kolkata'), (2, 'Kolkata', 'Banglore'), (3, 'Mumbai', 'Pune'), (4, 'Pune', 'Goa'), (5, 'Kolkata', 'Delhi'), (6, 'Delhi', 'Srinagar'); select * from flight_info; In this video...
SQL | Find the minimum and maximum salary in each company | #sql | Interview Problem | #dataanalyst
Переглядів 55512 годин тому
DROP table employee; Create the employee table CREATE TABLE employee ( emp_id VARCHAR(10), company CHAR(1), salary INT, dept VARCHAR(50) ); Insert data into the employee table INSERT INTO employee (emp_id, company, salary, dept) VALUES ('emp1', 'X', 1000, 'Sales'), ('emp2', 'X', 1020, 'IT'), ('emp3', 'X', 870, 'Sales'), ('emp4', 'Y', 1200, 'Marketing'), ('emp5', 'Y', 1500, 'IT'), ('emp6', 'Y', ...
SQL | find max, min population city names by state | SQL Interview Question | #dataanalyst | #sql
Переглядів 43912 годин тому
Learn how to write a SQL query to find the city with the maximum and minimum population for each state. Create the CityPopulation table CREATE TABLE CityPopulation ( id INT PRIMARY KEY, state VARCHAR(50), city VARCHAR(50), population INT ); Insert sample data into the CityPopulation table INSERT INTO CityPopulation (id, state, city, population) VALUES (1, 'Maharashtra', 'Mumbai', 1000), (2, 'Ma...
SAS | Counting Consecutive Free Seats: A SAS Solution | #sas #interview #programming #problem
Переглядів 5816 годин тому
In this video, we explore how to efficiently count consecutive free seats using SAS programming. We will walk you through a step-by-step process to analyze seat availability data, identify blocks of free seats, and present the results in a clear and concise manner. Whether you're a beginner or looking to enhance your SAS skills, this tutorial will provide valuable insights and practical techniq...
Identifying Employees Currently in the Office | #accenture | #sas | #interview #programming
Переглядів 7316 годин тому
Identifying Employees Currently in the Office | #accenture | #sas | #interview #programming
Ernst & Young | Transform Data with SAS | Pivoting and Aggregating | EY | Interview Problem | #sas
Переглядів 9016 годин тому
Ernst & Young | Transform Data with SAS | Pivoting and Aggregating | EY | Interview Problem | #sas
SAS | Employees Earning More Than Their Managers | SQL Interview Question | cognizant #dataanalyst
Переглядів 65919 годин тому
SAS | Employees Earning More Than Their Managers | SQL Interview Question | cognizant #dataanalyst
Find rows in one table that are not in another table | SAS | PROC SQL
Переглядів 78День тому
Find rows in one table that are not in another table | SAS | PROC SQL
Understanding UNION and UNION ALL in SAS | Essential Interview Question
Переглядів 52День тому
Understanding UNION and UNION ALL in SAS | Essential Interview Question
How to delete duplicates from tables I PROC SQL I SAS I Interview Problem
Переглядів 142День тому
How to delete duplicates from tables I PROC SQL I SAS I Interview Problem
Find Duplicate Rows Using PROC SQL I SAS Interview Preparation I SAS
Переглядів 135День тому
Find Duplicate Rows Using PROC SQL I SAS Interview Preparation I SAS
Barclays Interview Challenge | SAS Interview Question | Convert "Have" to "Want" Dataset
Переглядів 17414 днів тому
Barclays Interview Challenge | SAS Interview Question | Convert "Have" to "Want" Dataset
How to Extract Domains from Email Addresses | Barclays Interview Question | #sas
Переглядів 27914 днів тому
How to Extract Domains from Email Addresses | Barclays Interview Question | #sas
Second Highest Salary I SAS I Interview Problem
Переглядів 57014 днів тому
Second Highest Salary I SAS I Interview Problem
String Functions I COMPRESS, COMPBL, TRIM, and STRIP I SAS
Переглядів 3114 днів тому
String Functions I COMPRESS, COMPBL, TRIM, and STRIP I SAS
Understanding SCAN( ) Function in SAS I Interview Question
Переглядів 4814 днів тому
Understanding SCAN( ) Function in SAS I Interview Question
Translate and Tranward I SAS I Difference between TRANSLATE and TRANWRD Functions in SAS
Переглядів 3314 днів тому
Translate and Tranward I SAS I Difference between TRANSLATE and TRANWRD Functions in SAS
PROC SUMMARY vs PROC PROC I SAS Interview Question
Переглядів 2714 днів тому
PROC SUMMARY vs PROC PROC I SAS Interview Question
query to exclude weekends along with holiday dates, ;with cte_weekworkingdays as ( select ticket_id, issue_date, resolve_date, DATEDIFF(DAY, issue_date, resolve_date) + 1 as days_diff, DATEDIFF(WEEK, issue_date, resolve_date) * 2 as week_cnt, (DATEDIFF(DAY, issue_date, resolve_date) + 1) - (DATEDIFF(WEEK, issue_date, resolve_date) * 2) as weekworking_days from ticket_tbl ) select c.ticket_id, c.issue_date, c.resolve_date, c.weekworking_days, COUNT(h.holiday_date) as holiday_cnt, c.weekworking_days - COUNT(h.holiday_date) as actual_working_days from cte_weekworkingdays as c left outer join holiday_tbl as h on h.holiday_date between c.issue_date and c.resolve_date group by c.ticket_id, c.issue_date, c.resolve_date, c.weekworking_days order by ticket_id; happy querying :)
;with cte_employees as ( select emp.name as EmployeeName, mgr.name as ManagerName, emp.salary as EmployeeSalary, mgr.salary as ManagerSalary from salary as emp inner join salary as mgr on emp.manager_id = mgr.employee_id ) select EmployeeName from cte_employees where EmployeeSalary > ManagerSalary;
Thank you for contributing such a thoughtful solution! 🙌
;with cte_employees as ( select emp_id, company, salary, dept, ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary ASC) as rno_asc, ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary DESC) as rno_desc from employee ) select company as CompanyName, MAX(CASE WHEN rno_asc = 1 THEN salary ELSE NULL END) as Min_Salary, MAX(CASE WHEN rno_desc = 1 THEN salary ELSE NULL END) as Max_Salary from cte_employees group by company;
Thank you for sharing this solution! 🙌 Your approach using Common Table Expressions (CTEs) and ROW_NUMBER to find the minimum and maximum salaries for each company is both efficient and elegant. I especially like how you partitioned by company and used conditional aggregation to calculate the Min_Salary and Max_Salary. This solution is a great demonstration of the power of CTEs and window functions in SQL.
Make video about danse rank thing in sql by u
Thank you for the suggestion! I’ve already made a video covering DENSE_RANK, RANK, and ROW_NUMBER in SQL. You can check it out here: ua-cam.com/video/JobstiSrhfc/v-deo.htmlsi=NrIPsd2sI8js4_II. Let me know your thoughts or if you'd like any additional topics explained!
We can also do this using array
If you have a solution using arrays, please feel free to share it here so others can benefit as well. Thanks for contributing to the discussion!
👌
😊
Hi,why you didn't excluded weekend days? I mean 5 days working
Thank you for your suggestion! I appreciate your feedback. I will definitely consider creating another video that focuses on excluding weekend days to reflect a 5-day workweek. Stay tuned for more content, and thanks for your continued support!
;with cte_weekworkingdays as ( select ticket_id, issue_date, resolve_date, DATEDIFF(DAY, issue_date, resolve_date) + 1 as days_diff, DATEDIFF(WEEK, issue_date, resolve_date) * 2 as week_cnt, (DATEDIFF(DAY, issue_date, resolve_date) + 1) - (DATEDIFF(WEEK, issue_date, resolve_date) * 2) as weekworking_days from ticket_tbl ) select c.ticket_id, c.issue_date, c.resolve_date, c.weekworking_days, COUNT(h.holiday_date) as holiday_cnt, c.weekworking_days - COUNT(h.holiday_date) as actual_working_days from cte_weekworkingdays as c left outer join holiday_tbl as h on h.holiday_date between c.issue_date and c.resolve_date group by c.ticket_id, c.issue_date, c.resolve_date, c.weekworking_days order by ticket_id; hope this answers u r question..
select * from ( select p1.state,p1.city as maxPopulation ,p2.city as minPopulation, row_number() over (partition by p1.state order by p1.state, p1.population-p2.population desc)as rn from CityPopulation p1 inner join CityPopulation p2 on p1.state = p2.state and p1.population > p2.population ) where rn=1 ; ---- SELECT DISTINCT p1.state, FIRST_VALUE(p1.city) OVER (PARTITION BY p1.state ORDER BY p1.population DESC) AS MaxPopulation, FIRST_VALUE(p2.city) OVER (PARTITION BY p1.state ORDER BY p2.population ASC) AS minPopulation FROM CityPopulation p1 INNER JOIN CityPopulation p2 ON p1.state = p2.state AND p1.population > p2.population;
Many thanks for contributing this solution! 🙌
???we can use lead lag??why make it complicated
Thanks for your suggestion! I'll definitely focus on making future videos simpler. Using LEAD and LAG is a great idea, and I'll include those concepts in an upcoming video. Appreciate your feedback!
Please find the new solution using LEAD and LAG here: ua-cam.com/video/SSa5hwsfS2I/v-deo.html
@@CodeSQL great!
Thanks for uploading question. Hope to see more Vedios of solving problem.
I appreciate you watching! Stay tuned for more SQL interview problems.
Informative thank you , looking forward for more such informative content.😊
I'm glad you found it helpful! I'll keep creating more videos like this. 🙏
Grt one👍.......keep it up bro😍💯
Thanks for the support! I appreciate it! 🙏
i have a different approach of solving this problem. First, i performed inner join and found out the manager name for each employee. Then i used "where" clause to find out the salary comparision. select x.emp_id,x.name,x.sal as emp_sal,y.name as manager,y.sal as mgr_sal from a as x inner join a as y on x.mngr_id=y.emp_id where emp_sal>mgr_sal;
I have solved this problem earlier also but your way of explaining it with the diagram was fabulous, joins always create some kind of confusion while solving, specially self join, but the diagrammatic explanation was good, thank you.
I'm glad the diagram helped clear things up! 😊
Very helpful. ...keep posting sir
Thanks for watching! I'm glad you found it helpful.
excellent teaching
Thanks and welcome
@@CodeSQL bhaiya can you give me some advice . i am 2021 batch pass out . i have right now i have 4 years gap after graduation. currently my skills are PYTHON SQL NUMPY PANDAS and now i focus on POWERBI . IS THIS SKILLS IS GOOD TO GET A JOB BECAUSE I APPLY IN SO MANY COMPANY BUT NONE OF THEM REPLY . can you tell me what should i have to do and how much focus and how many projects should i make . or should i have to learn other skills
Focus on building 4-5 strong projects using Python, SQL, Pandas, and Power BI (e.g., data analysis, dashboards). Host them on GitHub or a portfolio. Add skills like Tableau, stats, or ML basics for better chances. Be honest about your gap-show how you’ve used the time to grow. Optimize LinkedIn, network, and apply for internships or entry-level roles. Stay consistent-results will come!
@@CodeSQL thankyou bhaiya i will work on this
What job position will ask this question? The software engineer or the Analyst? Thank you!
This question is asked for the Analyst role, specifically targeting candidates with 3-5 years of experience.
So, is this an Excel not PowerPoint? Looks very convenient.
Yes, this is Excel, not PowerPoint.
Grt 👍
I appreciate you watching! It means a lot to have your support. 🙏
Bro you doing good....please keep creating more videos. I have one request to you please create some questions on sas macros.
Thank you for your support! I'm glad you found the video helpful, and I'll definitely consider creating a video on SAS macros.
We can use scan (email,2,'@,.');
Thank you for this! We can definitely use scan(email, 2, '@,.') to extract the desired token from the email. Appreciate your input!
You can also try using scan function Scan(Scan(email, 2,"@"),1,".") ; Its simple and effective.
That's a great suggestion! The SCAN function is indeed a simple and effective way to extract parts of a string. In this case, using Scan(Scan(email, 2, "@"), 1, ".") will help extract the domain name from the email address, specifically up to the first dot after the "@" symbol. Thanks for sharing!
Sir kindly upload more interview questions and topic videos thank you 🙏
Thank you for your kind suggestion and support! 😊 I'll definitely work on creating more videos covering interview questions and important topics. Stay tuned, and feel free to share any specific areas you'd like me to focus on. Your feedback means a lot! 🙏
Bro I would like to know how you edited videos and presented.I would like to do for statistics videos.
@@venkatnaveen3267 You can use OBS Studio to edit your videos. It’s a great tool that allows you to record, stream, and make various edits to your footage. It should help you with most of your video editing needs!