Merge Products - SQL Interview Query 9 | SQL Problem Level "EASY"
Вставка
- Опубліковано 1 сер 2024
- 30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the NINTH video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.
Let's follow the below routine to make the best use of it:
1. Watch the UA-cam video (first half) to understand the problem statement.
2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.
3. Share your solution on Discord and discuss different solutions and issues on my Discord server.
4. Watch the second half of my UA-cam video to find my solution to the problem.
5. Share it with your contacts and spread the knowledge.
DOWNLOAD the Dataset from below:
Discord server: / discord
Blog website: techtfq.com/blog/30daysqlquer...
Timeline:
00:00 Intro
00:18 Understanding Problem Statement
03:40 Solution to the SQL Problem
Thanks for participating in this challenge!
Good luck and Happy Learning!
mysql user here. Its group_concat there as a replacement for the string_agg function in PostgreSQL
(select * from orders)
union
(select customer_id,dates,group_concat(product_id) as product_id
from orders
group by customer_id,dates)
order by dates,customer_id,product_id
My solution for MS SQL
with cte as (
select customer_id, dates, string_agg(product_id, ',') within group (order by dates) as products
from orders
group by customer_id, dates)
select dates, products from cte
union
select dates, cast(product_id as nvarchar(max)) from orders
very well explained thank you
I was thinking about the group_concat function. thank you for the series.
Thank You so much!.
Super, such a great 30 day initiative. I am into it alot and everyday i am eagerly waiting for your videos for new challenge 🎉🎉
🔥🔥🔥
Thank you for your outstanding presentations. Please make lectures on the Temporal Databases.
3 Solutions in Oracle:
1:
select dates,product_id from orders
union
select dates,listagg(product_id,',') as product_id from orders
group by dates,customer_id;
2:
select dates,product_id from orders
union
select dates,product_id||','||rn as product_id from(
select customer_id,dates,product_id,lead(product_id) over(partition by dates,customer_id order by dates) rn
from orders order by customer_id,dates,product_id) where product_idrn;
3: (Inspired from a comment😁)
select dates,product_id from orders
union
select B.dates,case when A.product_id
with cte as (
select *,lag(product_id) over (partition by customer_id,dates order by customer_id) as col
from orders)
select dates,cast(CONCAT(product_id,',',col) as varchar) as products from cte where col is not null
union
select dates,cast(product_id as varchar) as products from orders
order by dates ,products
Do leetcode - Hard level questions.
I am refining my SQL solving skill for interviews with this series of videos #30DaySQLQueryChallenge!
Where we use in realtime?
Oracle SQL Solution:
SELECT dates, TO_CHAR(product_id) AS products
FROM orders
UNION
SELECT dates, LISTAGG(TO_CHAR(product_id), ',') AS products
FROM orders
GROUP BY customer_id, dates
ORDER BY dates, products;
select dates, product_id
from orders
union
select dates, group_concat(product_id) as products
from orders
group by dates, customer_id
order by dates, product_id
Should the output not have two product_id = 101 on the 19th. Reason being both customer_id 1 & 2 get the product? (Sorry learning SQL and trying to understand)
Oracle SQL
LISTAGG()
with cte as
(select * ,
row_number() over(partition by customer_id,dates) as rn,lag(product_id,1)over() as lf
from orders),
cte1 as
(select dates,concat(product_id,' ',lf) as product_
from cte
where rn = 2)
select dates,product_id from orders union
select * from cte1
order by dates
it raises error, when you executes cte, separately, which says it requires order by clause.
with cte as
(
select *,
ROW_NUMBER() over(partition by customer_id, dates order by customer_id) as rn,
lag(product_id, 1, 0) over(order by customer_id) as prev_prod
from orders
),
cte1 as
(
select dates, concat(product_id,' ',prev_prod) as prod
from cte
where rn = 2
)
select dates, product_id
from orders
union
select * from cte1
order by dates
@@nikhilfromyoutube idk why , but It worked in MySQL
SQL SERVER SOLUTION:
SELECT dates AS DATES,CAST(product_id AS varchar) AS PRODUCTS
FROM orders
UNION
SELECT dates,string_agg(product_id,',')
FROM orders
group by customer_id,dates
select dates,concat_ws(',',product_id,lag_id) as product_id
from (
select customer_id,dates,product_id,lag(product_id)over(partition by customer_id,dates)as lag_id from public.customer_product
)a order by dates
create table b as
(with a as
(select customer_id ,dates, group_concat(product_id) as product_id
from orders group by customer_id , dates)
select dates, product_id from a) ;
select dates, product_id from orders
union all
select dates, product_id from b order by dates,product_id;
My solution: SELECT dates,product_id::STRING AS product_id FROM "ORDERS"
union
SELECT DISTINCT dates,list_prod AS product_id FROM
(select *,listagg(product_id,',') WITHIN GROUP (ORDER BY product_id ) OVER (PARTITION BY dates,customer_id) AS list_prod
from orders) ORDER BY dates,product_id;
MQSQL
SELECT dates, concat(product_id, ",", leading1) AS Products
FROM(
SELECT *,
LEAD(product_id) OVER(PARTITION BY customer_id, dates ORDER BY dates) as leading1
FROM orders) AS x
WHERE x.leading1 is not null
UNION(
select dates, product_id as Products from orders)
ORDER BY dates,Products;
with cte as
(select dates as DATES,product_id as PRODUCTS
from orders
union all
select r1.dates as DATES,concat(r1.product_id,",",r2.product_id) as PRODUCTS
from orders r1
join
orders r2
where r1.customer_id= r2.customer_id and
r1.dates=r2.dates and
r1.product_id != r2.product_id and
r1.product_id < r2.product_id)
select * from cte order by dates,products;