[@dakshbhatnagar]

mysql user here. Its group_concat there as a replacement for the string_agg function in PostgreSQL

[@kushmanthreddy4762]

(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

[@babag5324]

Super, such a great 30 day initiative. I am into it alot and everyday i am eagerly waiting for your videos for new challenge 🎉🎉

[@sunilnair846]

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

[@ollehbudiak7150]

Thank you for your outstanding presentations. Please make lectures on the Temporal Databases.

[@hassamulhaq7762]

I was thinking about the group_concat function. thank you for the series.

[@vishwasai546]

Is casting a key column like product id into varchar a good idea in real time scenario? I don't think so. Do we have another way to solve this?

[@12ravindrareddy]

Where we use in realtime?

[@rusmw]

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)

[@firdosesyed8981]

very well explained thank you

[@niharikamedam]

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

[@fathimafarahna2633]

🔥🔥🔥

[@vishwanath-ts]

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;

[@nileshpandey8247]

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

[@nikhilfromyoutube]

it raises error, when you executes cte, separately, which says it requires order by clause.

[@nikhilfromyoutube]

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

[@nileshpandey8247]

@@nikhilfromyoutube idk why , but It worked in MySQL

[@sunetala2957]

Thank You so much!.

[@splendidabhi]

I am refining my SQL solving skill for interviews with this series of videos #30DaySQLQueryChallenge!

[@subramanianks9030]

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

[@martinberger365]

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

[@dharmharley6871]

Do leetcode - Hard level questions.

[@NabeelKhan-um1zk]

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;

[@ShirleyShi-zb5us]

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

[@blse2000]

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

[@akshaysalve195]

with cte as (
(Select i.dates, group_concat(i.product_id) as product_id
from input_data i
group by customer_id,dates)
union
(Select i.dates, i.product_id
from input_data i
group by i.customer_id,i.dates, i.product_id))
Select * from cte
order by dates, product_id;

[@nadimatlanagesh1951]

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;

[@ratheeshg1896]

Oracle SQL
LISTAGG()

[@anniedhawan7945]

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;

[@7vensandy_Data_Analyst]

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;