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

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;

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

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;

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

with cte1 as( select case when role='bat' then [name] end as batsmen_name , case when role='bat' then performance end as score from Players_info ), cte2 as( select case when role='ball' then [name] end as bowler_name , case when role='ball' then performance end as wickets from Players_info ) select a.batsmen_name,b.bowler_name,a.rn from ( select top 100 percent *,row_number()over (order by score desc,batsmen_name asc) as rn from cte1 where batsmen_name is not null and score is not null order by score desc,batsmen_name asc )a join (select top 100 percent *,row_number()over (order by wickets desc,bowler_name asc) as rn from cte2 where bowler_name is not null and wickets is not null order by wickets desc,bowler_name asc )b on a.rn=b.rn

with cte as ( SELECT CASE WHEN ROLE='bat' THEN NAME END bat, CASE WHEN ROLE='bat' THEN performance END score, dense_rank() over(partition by role order by performance desc) rnk FROM PLAYERS_INFO where role='bat'), cte1 as ( select CASE WHEN ROLE='ball' THEN NAME END ball, CASE WHEN ROLE='ball' THEN performance END wicket ,dense_rank() over(partition by role order by performance desc) rk from PLAYERS_INFO where role='ball') select c.bat,c1.ball,rnk from cte c join cte1 c1 on c.rnk=c1.rk;

Can you please change your mic....sound quality is too bad to understand or focus

with cte as (select *, lag(temperature,1) over(order by date) as 1days, lag(temperature,2) over(order by date) as 2days from weather_data), cte2 as (select date,temperature,(1days+2days)/2 as avgs from cte) select date from cte2 where temperature > avgs

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

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

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;

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

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

Data analyst interview prep krva do

;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

with cte as (select a.date, a.temperature current_day_temp, b.temperature previous_day_temp, c.temperature two_days_before_temp from weather_data a left join weather_data b on datename(day,a.date)-1 = datename(day,b.date) left join weather_data c on datename(day,a.date)-2 = datename(day,c.date)) select date from cte where current_day_temp>(previous_day_temp+two_days_before_temp)/2

select * from himanshudb.employee a join (select department,avg(salary) as avg_salary from himanshudb.employee group by department) b on a.department = b.department and a.salary>b.avg_salary;

1st problem with cte as (select *,dense_rank() over(partition by department order by salary desc) as r from employees) select * from cte where r = 1;

with cte as(select *,(((lag(temperature,1) over(order by date))+lag(temperature,2) over(order by date))/2) as avg2_temp from weather_data) select date from cte where temperature>avg2_temp

with cte as(select name,role,performance, rank() over(partition by role order by performance desc) as rank from players) select p2.name as batsman,p1.name as bowler,p1.rank as rank from cte p1 join cte p2 on p1.rank=p2.rank and p1.role!=p2.role and p1.role='ball'

with cte as ( select *,dense_rank() over(partition by role order by performance desc ) as rank from Players_info), cte_2 as ( select string_agg(name, ' : ') as mark, rank from cte group by rank order by 2 asc) SELECT split_part(mark, ' : ', 1) AS Baller, split_part(mark, ' : ', 2) AS Batsman, rank FROM cte_2

with cte as ( select e2.empname, e1.empname as mgrname, e2.salary as empsal, e1.salary as mgrsal from employee e1 join employee e2 on e1.empid = e2.mgrid) select concat(empname,':',mgrname),(empsal + mgrsal) / 2 as salary from cte order by 2 desc offset 1 limit 1

with cte as ( select *, row_number() over(partition by role order by performance desc) as rnk, (case when role = 'ball' then name else null end) as batsman, (case when role = 'bat' then name else null end ) as ball from Players_info ) select max(batsman) as batsman, max(ball) as ballwer, rnk from cte group by rnk

hi sir this is my solution select user_id, count(1) as total_count , count(case when type = 'credit' then 1 end)*100/count(*) as credit_percentage, count(case when type = 'debit' then 1 end)*100/count(*) as debit_percentage from transactions12 group by user_id

with cte as ( select case when role='bat' then name end as batsmen, case when role='ball' then name end as bowler, rank() over (partition by role order by performance desc) as playerrank from players_info ) select max(batsmen) batsmen, max(bowler) bowler, playerrank from cte group by playerrank;

Without CTE from next video Solve both with CTE without CTE

Rider _name : select top 1 r.name, row_number() over(order by fare_amount/distance desc) as rider_per_km from trips12 t inner join riders r on t.rider_id = r.rider_id order by row_number() over(order by fare_amount/distance desc) Driver_name select top 1 d.name, row_number() over(order by fare_amount/distance asc) as driver_per_km from trips12 t inner join drivers d on t.driver_id = d.driver_id order by row_number() over(order by fare_amount/distance asc)

hi sir what i did first i create one table min(date), max(date) after that i join the table with sales_info then after i by using lead() function i solved the solution with cte as ( select customername , min(date) as first_date, max(date) as last_date from sales_info group by customername ) select *, ((lead(SalesAmount,1) over(partition by c.customerName order by c.first_date)) - s.SalesAmount )*100/s.SalesAmount as prev_amount from cte c inner join sales_info s on s.date in (c.first_date,c.last_date)

Nice Explanation.

select Date from (select date,temp,((temp1+temp2)/2) as avg_temp from (select date , temperature , lag(temperature) over(order by date) temp1 ,lag(temperature,2) over(order by date) temp2 from geo)GG )A where temp >avg_temp

WITH cte AS ( SELECT num, ROW_NUMBER() OVER (ORDER BY num) AS row_num COUNT(*) OVER() AS total_count FROM numbers ), mean_cal AS ( SELECT avg(num) AS mean FROM numbers ) mode_cal AS ( SELECT num AS mode FROM numbers GROUP BY num ORDER BY count(*) DESC LIMIT 1 ) SELECT (SELECT mean FROM mean_cal) AS mean, (SELECT mode FROM mode_cal) AS mode, AVG(num) AS median FROM cte WHERE row_num IN ((total_count + 1) / 2, (total_count + 2) / 2);

I think even for first question, where condition should include empid ! = mgrid For this specific table

Hello sir help mi to solve this question Write a query to display manager id who have 2 emplayee

Hi ,i have one doubt that one voter usually caste their vote for one candidate as it comes under one to many(or vice versa) relation. But in our scenario it has many to many relations which practically won't possible right ?

My solution: with cteone as ( select storeid, Salesamount as sales, date, datepart(week, date) as week, dense_rank() over(partition by storeid order by datepart(week, date) asc) as first_week, dense_rank() over(partition by storeid order by datepart(week, date) desc) as last_week from sales_info ), ctwo as ( select storeid, Sum(case when first_week = 1 then sales end) as first_amount, Sum(case when last_week = 1 then sales end) as last_amount from cteone group by storeid ) select storeid, round(((last_amount - first_amount) / first_amount) * 100, 2) as perc_increase from ctwo;