SQL Interview Question - Solution (Part - XXV) |

#dataanalyst #sqlfordataengineer #education #dataanalytics
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mah...
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)

[@srinivasulum414]

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

[@joemoneyweather4437]

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

[@motiali6855]

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;

[@lakshmanlee3579]

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

[@user-ju4ih5xr8e]

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

[@VARUNTEJA73]

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

[@vishnugottipati9373]

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

[@syedirfanali8043]

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

[@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

[@nd9267]

;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

[@MeanLifeStudies]

Looks more optimized than my solution

[@noobvirago3874]

PL SQL Developer Tool
With cte as
(
Select level as lvl from dual connect by level =lvl;

[@arjundev4908]

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;

[@harshitsalecha221]

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

[@shwetas4388]

Data analyst interview prep krva do

[@MeanLifeStudies]

Okay

[@MubarakAli-qs9qq]

For me its quite tough cte kaha se padu