CTE s IN SQL | Advanced SQL | Ashutosh Kumar
HTML-код
- Опубликовано: 16 окт 2024
- Sql one of the most important language asked in most of the analytics interviews,in this series i have discussed some advanced level sql concepts that are frequently asked in data analyst,business analyst interviews. In this video i have covered non equi joins concepts in sql
👉Query -
drop table if exists order_summary;
CREATE TABLE order_summary(orderid integer,amount integer,quantity integer);
INSERT INTO order_summary(orderid,amount,quantity)
VALUES (1,4922,8),
(2,7116,8),
(3,1206,4),
(4,2841,7),
(5,2522,2),
(6,5084,3),
(7,6680,4),
(8,8123,7),
(9,6015,2),
(10,4092,3),
(11,7224,2),
(12,7679,8),
(13,1303,2),
(14,5185,7),
(15,2139,8);
drop table if exists customer;
CREATE TABLE customer(cust_id integer,cust_first_name text,cust_last_name text);
INSERT INTO customer(cust_id,cust_first_name,cust_last_name)
VALUES (1,'Henry','Brown'),
(2,'James','Williams'),
(3,'Jack','Taylor');
drop table if exists orders;
CREATE TABLE orders(order_id integer,date date,cust_id integer);
INSERT INTO orders(order_id,date,cust_id)
VALUES
(1,'05-08-2020',1),
(2,'04-08-2020',2),
(3,'03-08-2020',3),
(4,'04-08-2020',1),
(5,'05-08-2020',2),
(6,'05-08-2021',3),
(7,'04-08-2021',1),
(8,'03-08-2021',2),
(9,'04-08-2021',3),
(10,'05-08-2021',2),
(11,'05-08-2022',1),
(12,'04-08-2022',2),
(13,'03-08-2022',3),
(14,'04-08-2022',1),
(15,'05-08-2022',2);
select * from orders;
select * from order_summary;
select * from customer;
👉 Complete playlist on Sql Interview questions and answers
• HackerRank SQL problem...
---------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here
👉 How to Learn SQL
• How to learn sql for b...
👉 How to become a business analyst complete roadmap-
• Business Analyst Compl...
👉 How to become a data analyst complete roadmap-
• Data Analyst Complete ...
👉 Top 3 you tube channels to learn sql for free for beginners
• Video
👉 Rank ,Dense Rank, Row Number in sql -
• RANK - DENSE RANK - RO...
👉 Cross join in sql
• CROSS JOIN SQL
👉 union join in sql
• UNION IN SQL
👉 left join in sql
• LEFT JOIN IN SQL
👉 Right join in sql
• RIGHT JOIN IN SQL
👉 Inner join in sql
• INNER JOIN IN SQL
👉 Introduction to tables and databases in sql -
• INTRO TO TABLES AND DA...
👉 Aggregate Function in sql
• AGGREGATE FUNCTION IN SQL
👉 Functions in sql-
• IMPORTANT FUNCTIONS IN...
👉 String Function in sql
• STRING FUNCTIONS IN SQL
👉 CRUD operations in sql
• CREATE- READ- UPDATE-D...
👉 Autoincrement in sql
• Auto Increment in SQL ...
👉 Primary Key in sql-
• PRIMARY KEYS IN SQL - SQL
👉 Null and Default values in sql-
• NULL AND DEFAULT VALUE...
👉 Data types in sql-
• Data types in Sql - SQL
____________________________________________________________________
Fill the form below to subscribe yourself to the analytics jobs mailing list to receive regular job opening updates - docs.google.co...
Why you should definitely fill the analytics job updates google form - • Job Openings into busi...
_______________________________________________________________________
Connect with me
📸Instagram - / ashutosh.analytics
💻Linkedin- / ashutoszh
_____________________________________________________________________
Comment down if you have any doubts
Please leave a LIKE 👍 and SUBSCRIBE ❤️ to my channel to receive more amazing content in data analytics and data science.
_____________________________________________________________________
🏷️ Tags
sql,
sql for data science,
sql for data analytics,
sql practise questions,
sql practise questions and solutions,
sql tutorials for beginners,
sql problems for data engineers,
ashutosh,
ashutosh kumar,
ashutosh kumar analytics,
sql problems easy,
sql problem medium,
sql problems hard,
sql window functions,
sql advanced questions,
rank functions in sql,
lag lead in sql,
sql interview questions and answers,
sql interview questions,
sql questions asked in interviews,
hackerrank sql solutions,
hackerearth sql solutions,
leetcode sql solution
🏷️HashTags
#sql #interviews #questions #solutions
Awesome tutorial on cte, concise and crystal clear. It was a bit frustrating for me working in sql and not being able to chain the results or save the objects to be reused. From now on, after your cte tutorial, it will be more funny and pleasant working with sql as never before.
For greater readibility, I would like to suggest you to consider some formatting or beautifying your code, specially your inner inner query/the derived table from the first join
Keep it up Sir, you are a legend!
you are one of the best teachers on youtube just a request to make videos in hindi as well
thanks
Patiently and Detailed Explanation. Please make a video on Dynamic SQL as well
Thanks a lot
@@AshutoshKumaryt Please make videos on Stored Procedures, Triggers, ETL also
Hello Ashutosh,
I want to know whether the Syntax of this advance SQL tutorials will be same for MySQL as well?
Please reply.
Thanks in Advance.
Yes
Did u know we love ur teaching and content
Thanks
Thank u. Pls make more videos
Thank you
Can you provide video for indexing as well
sir me Excel, SQL and Power Bi sikna complete kiya he lekin job nahi mil rahi he 😢 keya karo
keep applying one day you will get success
Bro can i use mongo db campus for data analytics jobss or i have to do it with other language means who will get more preferrence
And one more ques ...
Are you doing any job r8 now ??
To the point! Thanks bro
Thank you
@@AshutoshKumaryt Expecting a detailed video on UNION & UNION ALL
@@sabarinathan594 noted
Teaching and content quality is best, but less views Keep doing it bro
thanks
Bhai ne into into mei jhol kar ditta
Jara detail mein btao bhai kya jhol ho gya
In PostgreSql, We can do
select
c.cust_id,
concat(c.cust_first_name, ' ', c.cust_last_name) as full_name,
SUM(os.amount * os.quantity) filter(where date_part('year', o."date") = '2020') sale_2020,
SUM(os.amount * os.quantity) filter(where date_part('year', o."date") = '2021') sale_2021,
SUM(os.amount * os.quantity) filter(where date_part('year', o."date") = '2022') sale_2022
from customer c
join orders o on o.cust_id = c.cust_id
join order_summary os on os.orderid = o.order_id
group by 1,2
order by 1