SQL Interview Question - Solution (Part - XXV) | #sql #dataanalyst #dataengineers #education

#dataanalyst #sqlfordataengineer #education #dataanalytics

Here are my profiles that will definitely help you prepare for data analyst or data engineer roles.

Table Create and insert statements:
----------------------------------------------------------
create table items (item varchar(20), item_count int)
insert into items values ('Ball', 2),('Bat', 4), ('Glouse', 1), ('Wickets', 3)

[hrishi]

this concept was new to me, thanks man

[srinivasulum]

with cte as (
select item, item_count, 1 as num from items
union all
select item, item_count, num+1 from cte
where num+1<=item_count
)
select item, num from cte
order by item, item_count, num

[santoshkumar-pkxt]

Oracle :

Select distinct item, level from items connect by level <= item_count order by item

[joemoneyweather]

With cte as
(
select item, item_count from items
union all
select item, item_count-1 from cte
where item_count>=2
)
select * from cte
order by item, item_count

[lakshmanlee]

hi sir i used this code for solving this problem:

code:
with recursive cte as
(
select item, 1 as total
from items
union all
select distinct c.item, total + 1 as total
from cte c join items i on c.total < i.item_count and c.item = i.item
)

select *
from cte
order by item, total

[torrentdownloada]

with cte as(select item, item_count from items
union all
select item, item_count-1 from cte where item_count>1)
select * from cte order by item, item_count

[vishnugottipati]

with cte as(
select item, item_count from items1
union all
select cte.item, cte.item_count-1 from cte inner join items1 i on cte.item=i.item where cte.item_count>1
)
select item, item_count from cte order by item, item_count

[VARUNTEJA]

with r_cte as
(select item, 1 as number, item_count from items
union all
select item, number+1, item_count from r_cte
where number<item_count )
select item, number from r_cte order by item, number

[vishalsvits]

with cts AS (
select item, item_count, 1 AS Ctn From items
union all
select c.item, c.item_count, c.ctn+1 from items i
join cts c on c.item_count =i.item_count and i.item_count>c.Ctn
) select item, ctn From cts
order by item

[manjunatharalikatti]

;WITH CTE
AS
(
SELECT item, 1 AS cnt, item_count FROM items
UNION ALL
SELECT item, cnt+1 AS cnt, item_count from CTE WHERE cnt < item_count
)
SELECT * FROM CTE order BY item, cnt, item_count

[syedirfanali]

Sir i am beginner in SQL
Request to post beginner tutorial 🙏🏻please sir
I want to learn SQL

[motiali]

My Solution:

with recursive cte as (
select item, item_count, 1 as level from items
union all
select cte.item, cte.item_count-1, (level+1) as level
from cte
where cte.item_count>1
)
select cte.item, cte.item_count from cte
order by cte.item, cte.item_count;

[harshitsalecha]

WITH recursive cte1 AS (SELECT item, item_count, 1 as number FROM items
UNION ALL
SELECT item, item_count, number+1 FROM cte1
WHERE number<item_count)
SELECT item, number FROM cte1
ORDER BY item;

[arjundev]

WITH RECURSIVE CTE AS(SELECT *, 1 AS JUM
FROM ITEMS
UNION ALL
SELECT C.ITEM, C.ITEM_COUNT-1, JUM+1 AS LEVEL
FROM CTE AS C
JOIN ITEMS AS I
ON C.item_count = I.item_count
WHERE C.ITEM_COUNT > 1)
SELECT * FROM CTE
ORDER BY 1;

[noobvirago]

PL SQL Developer Tool

With cte as
(
Select level as lvl from dual connect by level <=(select max(item_count) from items
)
Select item, lvl from cte join items on item_count >=lvl;

[nd]

;with cte as
(
select item, sum(item_count) total_count, 1 as level from items_item
group by item

union all

select item, total_count, level+1 from cte
where level+1 <= total_count
)
select item, level as item_count from cte
order by item

[MubarakAli-qsqq]

For me its quite tough cte kaha se padu