Lovely to see a solution in sql for something I have solved in excel for years.

@Howto-ty4ru

Жыл бұрын

True

Hi Ankit, I solved it using running sum with t1 as ( select *,row_number() over () rn from brands ), t2 as ( select *,sum(case when category is not null then 1 else 0 end) over (order by rn) flag from t1 ) select max(category) over (partition by flag) as category,brand_name from t2

Hi Ankit, I have solved this using below method, you have tought this method for another problem. with cte1 as (select *, row_number() over(order by (select null)) as id, case when category is null then 0 else 1 end as rn from brands ) ,cte2 as (select * ,sum(rn) over(order by id) as roll_sum from cte1 ) select brand_name, max(category) over(partition by roll_sum) as category from cte2

Ever thanks for the invaluable example

using only window function: with cte as(select *, ROW_NUMBER() over(order by (select 1)) as id from brands), cte2 AS (select *, sum(case when category is NULL then 0 else 1 end) over(order by id) as part from cte) SELECT *, FIRST_VALUE(category) over(partition by part order by id) as fill_NA from cte2

Hi Ankit, I have another solution to this problem: with cte1 as ( select * , row_number() over(order by (select null)) rn from brands ) select min(category) over(order by rn rows between unbounded preceding and current row) category, brand_name from cte1 Wasn't really confident using rows clause but thanks to your videos I am learning new concepts everyday.

@ankitbansal6

Жыл бұрын

Awesome

@stat_life

Жыл бұрын

@@ankitbansal6 with temp as ( select * , row_number() over(order by (select null)) rn from brands ) select min(category) over(order by rn) category, brand_name from temp sir, won't this query pretty much gives the same solution? why did we used rows between unbounded preceding and current row here ?

@LittleBrainFeeds

Жыл бұрын

Good solution

@subhojitchatterjee6312

Жыл бұрын

Easy to understand ,but this will not work in all cases. If the category name changes something to "Freshly Packed" from "Biscuits" then it won't work.

@ank_kumar12

8 ай бұрын

i got the same result from this query with tab1 as (select *, count(t.category) over(order by t.rnk) as cnt from( select *, row_number() over() as rnk from choco) t) select category,brand, first_value(category) over(partition by cnt order by rnk) from tab1;

Hi, Help me to understand how did inner join between CET1 result and CTE 2 result replaced NULL with category name.. If possible please make a short video on query execution.

Thank you so much Sir for the wonderful query

Hi Ankit, I have another solution I am using running sum to make all nulls be part of same group as of previous non null value (I learned this from you only) with brands_rn as ( select *, row_number() over() as rn from brands ), brands_grouped as ( select *, sum(case when category is null then 0 else 1 end) over(order by rn) as gpno from brands_rn ) select max(category) over(partition by gpno) as category, brand_name from brands_grouped

I don't believe any solution should rely on the default return order of data to make an assumption on what the correct value would be. In this situation, the only proper course of action is to find the original source data, manually identify, or use a master data source of all chocolate & biscuit brands to match and validate.

Such a wonderful trick 👍

using CTE and aggregate (count) window function with cte as( Select * ,count(category)over(order by (Select null) rows between unbounded preceding and 0 following ) as rn from brands ) Select first_value(category)over(partition by rn order by rn ) as category, brand_name from cte

hi ankit, i need to get clarification Microsoft Sql Server is the platform where we can write the sql queries or what is. Microsoft sql server can you give me a brief idea about this

Hey dear, god bless your efforts in this channel. I have a general enquiry as a new sql learner. How could i create a pipeline to extract and load data from existing accounting program into our SQL server instances. How can i know if the export mechanism in the software permits me to undertake this extraction process, and how can i know if an application have an api? Thanks for taking care of my enquires. Looking forward to gain more knowledge from you.

Hi Can you check this: select max(category) over(partition by ct),brand_name from ( select category,brand_name, count(category) over(rows between unbounded preceding and current row) ct from brands )q

WITH temp1 AS (SELECT *, Row_number() over( ORDER BY NULL) AS start FROM brands), temp2 AS (SELECT * FROM temp1 WHERE category IS NOT NULL), temp3 AS (SELECT *, ( Lead(start, 1, 9999) over ( ORDER BY start) - 1 ) AS END FROM temp2) SELECT b.category, a.brand_name FROM temp1 a join temp3 b ON a.start BETWEEN b.start AND b.END;

Much needed for today 😂

Good use case to practice Ankit, I practiced using combination of rowunm ,join and understood how it works and then able to come up with one line solution . Done using oracle - select nvl(category,lag(category) ignore nulls over(order by null) ) category,brand from brands ; or select category,brand,rownum rn1, last_value(category) ignore nulls over ( order by rownum asc rows between unbounded preceding and current row ) from brands; Your solution helped to derive the approach. So though I knew how last_value , first value worked , first time I found out use case where rows between... clause and ignore null was useful . Thanks for providing this example!

@ankitbansal6

2 жыл бұрын

Great 😊

@namanmakkar1205

2 жыл бұрын

Can you explain ignore null

@swapnilshimpi7588

2 жыл бұрын

@@namanmakkar1205 Ignore null , as indicates it ignores null in that window and looks for rows with actual value

@shekharagarwal1004

Жыл бұрын

@@swapnilshimpi7588 Can you help to get the same in MY SQL syntax. It is not accepting the ignore null part .

with CTE1 as (select *,row_number()over() as rn,case when lead(category)over() is null and category is not null then 1 else 0 end as num from brands), CTE2 as (select category,brand_name,sum(num)over(order by rn) as sm from CTE1) select b1.category,b2.brand_name from CTE2 b1 left join CTE2 b2 on b1.sm=b2.sm where b1.category is not null

great question !!

Thanks a lot Ankit! Yout vídeo simply solved a problem that i had to make a report query on my job! THANK YOU! =D

@ankitbansal6

9 ай бұрын

Happy to help😁

In this approach, I am using co-related subquery. I haven't use any join. This code is very small......... with t1 as (select *, row_number() over() rn from brands) select (case when category is null then (select category from t1 b where b.rn else category end) category, brand_name from t1 a

@ankimedia5225

Жыл бұрын

What sub query will return please explain

@ankimedia5225

Жыл бұрын

@Subham Agrawal

Very interesting what you mention, which gives me hope to become better as solving SQL problems. You said you could not solve this problem. I guess I have to work harder on the ones I can not solve and work harder. Thanks for the inspiration, as usual.

@ankitbansal6

Ай бұрын

There is always a learning curve 😊

Hi Ankit, can we do this way........ Select (case when rn>1 and rn5 and rn

select (case: when category is null then category= lag(category) over() else category end) as category, chocolate from table.

I used the same technique like the ON OFF problem here, tried to maintain a simple strategy for creating a sequence for creating groups. Its just easier for me to remember. with cte as (select *,row_number() over() as rowed from brands), cte2 as ( select * ,sum(case when category is not null then 1 else 0 end) over(order by rowed) as grouped from cte) select max(category) over (partition by grouped),brand_name from cte2;

@ankitbansal6

Жыл бұрын

Good one

@siddheshkalgaonkar2752

Жыл бұрын

Interesting solution, mate. I was surprised to see that max works on the string too...

Good problem, Ankit :)

other solution using first value:- with cte as ( SELECT category,brand_name, row_number() over(ORDER BY NULL) as rn FROM brands ) select category,brand_name, first_value (category) IGNORE NULLS over(order by rn desc range between current row and unbounded following) as result FROM cte ORDER BY rn ;

looking a way to create a dynamic sql and do it on every column of a table

Hi Sir My Way: select case when partition_flag = 1 then 'choclates' when partition_flag = 2 then 'Biscuits' end as 'category', brand_name from ( select *, case when category is not null then 1 else 0 end as flag, sum(case when category is not null then 1 else 0 end) over(rows between unbounded preceding and current row) as partition_flag from brands)temp;

with cte as (select *, row_number() over(order by (select null)) as rnum from brands), cte2 as (select *, sum(case when category is not null then rnum end) over(order by rnum) as flag from cte) select first_value(category) over(partition by flag order by flag) as category_new, brand_name from cte2

with cte as ( select *, row_number() over() as rn from brands ) select case when rn between 1 and 4 then 'chocolates' else 'biscuits' end as category, brand_name from cte; i did like this is this correct

I tried to solve in different way and here my solution :;with cte as (select ROW_NUMBER() over(order by brand_name) as rw,* from brands) , cte2 as( select category ,brand_name,sum ( case when category is null then 0 else 1 end ) over(order by rw) rww from cte) select FIRST_VALUE(category) over(partition by rww order by (select 1)) as Category, brand_name from cte2

used between for join: with cte as( select *,ROW_NUMBER() over(order by (select null)) rn from brands) ,cte1 as( select *,lead(rn-1,1,9999) over(order by rn) btw from cte where category is not null ) select c1.category,c.brand_name from cte c inner join cte1 c1 on c.rn between c1.rn and c1.btw

@ankitbansal6

2 жыл бұрын

Looks good

Easiest approach would be : with cte as (select *,ROW_NUMBER() over(order by (select null))rn from brands),cte2 as ( select *,count(category) over (order by rn) cnt from cte) select *,FIRST_VALUE(category) over (partition by cnt order by cnt)result from cte2

Hi sir you can use the first_value() function to solve this issue in a simple way.

with cte as ( select category,brand_name, row_number() over (order by (select null)) as product_id from brands) , cte2 as ( select category,brand_name,product_id, count(category) over (order by product_id) as category_group from cte) select first_value(category) over (partition by category_group order by product_id) as category, brand_name from cte2;

select category1,brand_name from ( select * ,max(category) over(partition by t ) as category1 from ( select *,sum(case when category is not null then 1 else 0 end ) over(order by rn) as t from (select *,row_number() over() as rn from brands)))

Hi Ankit, row_number with ordering by (select null) does not work in redshift SQL. Redshift considers the whole table under order by and then gives row numbers. Is there any alternative to designate such serial numbers which will work in redshift?

@ankitbansal6

2 жыл бұрын

You can do order by true in redshift

this works but does it a good query??? with a as ( select *, row_number() over() as remark from brands) select if (category=null,'chocolates','chocolates') category,brand_name from a where remark between 1 and 4 union all select category,brand_name from ( with b as ( select *, row_number() over() as remark from brands) select if (category=null,'Biscuits','Biscuits') category,brand_name from b where remark between 5 and 7) as abc

Hi, here is my solution: with cte as (select *, row_number() over(order by (select null)) as rn from brands) select first_value(category) over(partition by partition_variable order by rn) as Category, brand_name from (select *, sum(case when category is null then 0 else 1 end) over(order by rn) as partition_variable from cte) A

Bhai video dekhta hu to sab samajh me aa jata hai par kuchh din baad bhul jata hu

;with cte as( select *,ROW_NUMBER()over(order by (select null)) as rw from brands ),cte1 as( select *,count(category)over(order by rw) as cnt from cte ) select *,FIRST_VALUE(category)over(partition by cnt order by rw) from cte1

using mysql with cte as (SELECT category, brand_name,row_number() over () as rn FROM brands) ,cte1 as (select *,sum(case when category is not null then 1 else 0 end) over (order by rn) as ct from cte) select first_value(category) over (partition by ct order by rn) as category,brand_name from cte1

Bhai ! one thing I want to know is row_number() fun also work with over() clause without adding order by in it , what is the difference between the empty over() and over(order by (select null)). btw .... thanks u so much

@ankitbansal6

2 жыл бұрын

Doesn't work in SQL server

this should work fine: SELECT COALESCE(category, LAG(category IGNORE NULLS) OVER (ORDER BY brand_name)) AS category, brand_name FROM your_table_name;

Hi Ankit, I solved this problem using user defined variable with case statement. Please check my approach if its efficient - select case when category is not null then @category := category when category is null then @category end as category1, brand_name from brands;

with base as ( Select *, case when category is not null then 1 else 0 end as flag from brands ), base2 as ( Select *, sum(flag) over(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_sum from base ) Select first_value(category) over(partition by running_sum) as category, brand_name from base2

@ankitbansal6

Жыл бұрын

Good one 👍

can you do a lead lag master class video?

@ankitbansal6

2 жыл бұрын

It's there already

Hi Ankit can we do this, if we just make an indexing column like you did with rn and then according to the index we use set function and update the value at once...can we do that

@ankitbansal6

Жыл бұрын

Try writing the query 😊

Row_number() over (order by null) is changing the order of names of item and not giving numbering to the exact order that is in table in Netezza. Any suggestions . Tried replacing null with 1, true but still order gets mixed up

@no-nonsense-here

Жыл бұрын

try just row_number() over()

That saved my ass. Thx ❤

HI, I solved that by recursive cte 🙂 with a as ( select * ,case when category is not null then row_number() over(order by (select null)) end crn ,row_number() over(order by (select null)) cn from brands), b as ( select max(cn) mxcn from a), c as ( select category, crn mn, lead(crn,1, (select mxcn from b)+1) over(order by crn)-1 mx from a where category is not null), rec as ( select category, mn, mx from c union all select category, mn+1, mx from rec where mn

I tried solving this using while loop, try this one: select *,row_number() over(order by (select null)) as rw into #brands from brands --drop table #brands select * from #brands declare @rw int set @rw=1 while (@rw

ANother Simpler way!!! select * , MIN(category)over(order by (select NULL) rows between unbounded preceding and current row) as NEW_CATEGORY from brands;

@udhaybhaskarbellamkonda1678

3 ай бұрын

This works ,great

I derived by the same solution Sir!!!!!!!!!! WITH CTE_1 AS (Select *, ROW_NUMBER() OVER(order by (SELECT NULL)) as rnk from brands), CTE_2 AS (Select category, rnk, COALESCE(LEAD(rnk) OVER(order by rnk),999999) as next_one from CTE_1 WHERE category IS NOT NULL) Select B.category, A.brand_name FROM CTE_1 A JOIN CTE_2 B ON A.rnk >= B.rnk AND (A.rnk

Alternate Solution: with structureCte as( --creating structure/skeleton so that "order by" can be used select *, ROW_NUMBER() over(order by(select null)) as rn from brands ) ,categoryGrp as( select *, count(category) over(order by rn asc rows between unbounded preceding and current row) as grp --grouper(so that partitioning can be done) from structureCte ) select FIRST_VALUE(category) over(partition by grp order by rn) as Category_filled, --Get First_Value in a particular group categoryGrp.brand_name from categoryGrp

Hi Ankit , here is my solution with CTE as ( Select category, Brand_Name, ROW_NUMBER () over (order by (select 0)) as rn from brands) select brand_name, min(category) over (order by rn) as New_Category from CTE

@ankitbansal6

2 жыл бұрын

This is working with this particular data only take one more category with name starts from C or D

Thanks Ankit but we can write it another way 🙂..simple with CASE statement. WITH t1 AS( select category, brand_name, row_number() over(order by (select null from dual)) as rnk from brands) SELECT CASE WHEN rnk = 1 THEN 'chocolates' WHEN rnk = 2 THEN 'chocolates' WHEN rnk = 3 THEN 'chocolates' WHEN rnk = 4 THEN 'chocolates' ELSE 'Biscuits' END AS category, brand_name, rnk FROM t1;

@ankitbansal6

Жыл бұрын

But you have hard coded the values here. What if more products come in tables

with cte as (SELECT * , SUM(CASE WHEN category IS NULL THEN 0 ELSE 1 END) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) flg FROM brands) SELECT FIRST_VALUE(category) OVER(PARTITION BY flg) , brand_name FROM cte; How about this one Ankit?

@siddhartharao8490

2 ай бұрын

Thanks bro for the brief explanation!

MY MYSQL Solution with base as (select *,case when category is not null then 1 else 0 end as flag from brands), base_rank as ( select *,sum(flag) over(rows between unbounded preceding and current row) as run_sum from base ) select coalesce(category,max(category) over(partition by run_sum)) as category,brand_name from base_rank;

WITH CTE AS (SELECT *,NTILE(2) OVER() as Bucket from Your_Table) ,CTE2 AS (SELECT * ,CASE WHEN (Bucket = 1 and category IS NULL) or (Bucket = 1 and category IS not NULL) then 'Choclate' else 'Biscuite' end as Category1 from CTE) SELECT category1 AS category ,brand FROM CTE2

SELECT a.category, a.brand_name, MAX(a.category) OVER (PARTITION BY a.RN ORDER BY a.RN) newcategory FROM ( SELECT * , SUM(CASE WHEN COALESCE(category,'') = '' THEN 0 ELSE 1 END) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING and current row ) RN FROM brands ) a ;

but we have first_value() in sql

here is my solution with t1 as ( select *,row_number() over (order by (select null)) rn from brands ), t2 as ( select *,sum(case when category is not null then 1 else 0 end) over (order by rn) flag from t1 ) select * from t2 select max(category) over (partition by flag) as category,brand_name from t2

with cte as (select *, COUNT(category) over (order by rn)as cnt from (select *, row_number() over (order by (select null))rn from brands)a) select first_value(category) over (partition by cnt order by rn)category_new , brand_name from cte

my solution: with cte as (select *,row_number() over () as num from deloitte_brands), brands as (select category as type,num as row_num from cte where category is not null), cte2 as (select *,lead(row_num,1,9999) over() as next_row_num from brands) select type,brand_name from cte2,cte where (cte.num >= cte2.row_num and cte.num < cte2.next_row_num ) order by type;

select count(category) as category from brands; set @cat = 2; with cte as (select category,brand_name, ntile(@cat) over() as cat from brands) select brand_name, first_value(category) over(partition by cat) as category from cte hope you like this solution

Just an observation To get row number on the whole table the over clause can be left blank too. select *, row_number() over() from table;

select category,brand_name from (select brand_name, case when brand_name in ('5-Star','dairy milk','perk','eclair') then 'choclates' else 'Biscuits' end as category from brands1)x Hi Ankir Sir cant we do like this please verify

@ankitbansal6

Жыл бұрын

It's hard coding. Write a generic code

SELECT CASE WHEN ROWNUM IN (2, 3, 4) THEN 'chocolates' WHEN ROWNUM IN (6, 7) THEN 'Biscuits' ELSE CATEGORY END AS CATEGORY, BRAND_NAME FROM brands; working fine in Oracle Sql.

SELECT @k := IF(category is not null, category, @k) as category, brand_name FROM table;

In ct2 there is no rn , how can we write cte1.rn>ct2.rn ? , in ct2 we have just next_rn

@anuragkumar-ti1vz

Жыл бұрын

In cte2 we are doing select * from cte1 so all columns of cte1 can be accessed from cte2. Now cte1 & cte2 became 2 tables so in the final select we can pick any columns from both tables.

My Solution Using First_value(): with brand as ( select category,COUNT(category) OVER(order by b.rn) as cnt,brand_name,rn from( select *,ROW_NUMBER() OVER(order by NULL) as rn from brands) as b ) select first_value(brand.category) over(partition by brand.cnt order by brand.rn) as category,brand.brand_name from brand

MySQL solution (Would work in MSSQL as well if 'IF' is replaced by 'Case') WITH cte_1 AS ( SELECT category, brand_name, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn FROM brands ), cte_2 AS ( SELECT *,MAX(IF(category IS NOT NULL, rn, NULL)) OVER(ORDER BY RN) AS grp FROM cte_1 ) SELECT MAX(category) OVER(PARTITION BY grp ORDER BY rn) category, brand_name FROM cte_2

/* simple and easy approach*/ select first_value(category) over (partition by new_count order by rn ) as category ,brand_name from ( select *, count(category) over (order by rn) as new_count from ( select *, row_number () over (order by (select null)) as rn from brands )A)B

use coalesce function which is more simpler than any other process

@rabink.5115

Жыл бұрын

could you please update with the queries. It will help a lot. Thanks

@ShrinchaRani

Жыл бұрын

@@rabink.5115 WITH CTE AS ( select *, ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS rn from bars ) SELECT category,brand_name from ( SELECT coalesce(category,CASE WHEN rn

In power BI, you just need to click on the column and click 'Fill values'. Simple.

@ankitbansal6

2 жыл бұрын

Cool

@ishanksaxena3329

Жыл бұрын

But Ankit said that it's a SQL interview so we have to solve this problem with sql😄

@vikaskumar-qr5tj

Жыл бұрын

🤣

@Kishansinghbhandari

Жыл бұрын

​@@ishanksaxena3329😂

@gunasekharreddy230

9 ай бұрын

Hello , This Problem was asked to do in DAX for me in EXL Interview

WITH cte1 AS (SELECT *, ROW_NUMBER() OVER(ORDER BY null) AS id FROM brands), cte2 AS (SELECT *, COUNT(category) OVER(ORDER BY id) AS grp FROM cte1) SELECT MAX(category) OVER(PARTITION BY grp ORDER BY id) AS category, brand_name FROM cte2

Simple solution: select MIN(category) OVER (ORDER BY (SELECT null) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS category, brand_name FROM brands;

@chetanphalak7192

7 ай бұрын

Why cant we use max, can u please explain a bit

Hi, I badly need help to solve 2 SQL problem. I'll drop the problems here. If you know how to solve that it would help me a lot. 1. You are given two tables, Medicine and Hospital. Medicine table containing, (ID, name, exp_date, cld) Hospital table containing,(CID, branch,name) Write a Query to display the Hospital name and count of medicines that it procures in ascending order of the hospital name. 2. Given two tables, Orders and Customers Order table containing (Order_No, Purchase_amt,Cust_id,Salesman_id) Customer table containing (cust_id,cust_name,city,grade,salesman_id) Write a Query to display Customer's I'd followed by the grades of the customers who placed the order sort the record as per the grades in descending order.

@shekharagarwal1004

Жыл бұрын

Thanks Ankit and really grateful to you for your knowledge sharing session. Hi Anitha - 1 ) Write a Query to display the Hospital name and count of medicines that it procures in ascending order of the hospital name : Please advise as I am unable to get the quantity field in Medicine table. I used the below dataset to arrive to the solution -------------------------------------------------- Table : Medicine -------------------------------------------------- ID,name,exp_date,cld,qty -------------------------------------------------- M1,Crocin,10-09-2023,H1,200 M2,Omnee,10-09-2023,H1,100 M3,ibufropen,10-09-2023,H1,205 M4,sumo-cold,10-09-2023,H1,140 M3,ibufropen,10-03-2023,H1,50 ==> This medicine-M3 is repeat prescription by Hospital H1 M1,Crocin,10-09-2023,H2,300 M2,Omnee,10-09-2023,H2,230 M3,ibufropen,10-09-2023,H2,100 M4,sumo-cold,10-09-2023,H3,350 -------------------------------------------------- Table : Hospital -------------------------------------------------- CID, branch,name -------------------------------------------------- H1,Kolkata,Apollo H2,Chennai,Fortis H3,Delhi,AIIMS H4,Kolkata,ILS -------------------------------------------------- Result : -------------------------------------------------- HospitalName,Total_Medicine_qty,Medicine_type_count -------------------------------------------------- Kolkata,695,4 Chennai,630,3 Delhi,350,1 -------------------------------------------------- Psuedo Query : SELECT H.Name as HospitalName , sum(M.qty) as Total_Medicine_qty , count(distinct M.ID) as Medicine_Type_Count FROM Medicine M INNER JOIN Hospital H ON M.CID=H.CID GROUP BY H.Name ORDER BY H.Name; 2) Above approach will be torch-bearers to solve this problem.

; WITH cte_seq AS ( SELECT * ,row_number() OVER ( ORDER BY ( SELECT NULL ) ) AS rw FROM brands ) ,cte_null AS ( SELECT * ,sum(CASE WHEN category IS NULL THEN 0 ELSE 1 END) OVER ( ORDER BY rw ) AS rwn FROM cte_seq ) SELECT FIRST_VALUE(category) OVER ( PARTITION BY rwn ORDER BY rwn ) AS Category ,brand_name FROM cte_null

My solution is more simple i think: WITH CTE AS( SELECT *, ROW_NUMBER() OVER() as rn FROM Brands ) ,CTE1 AS (SELECT *, SUM(CASE WHEN category is NULL THEN 0 ELSE 1 END) OVER(ORDER BY rn) as sm FROM CTE) SELECT FIRST_VALUE(category) OVER(PARTITION BY sm ORDER BY rn) as Brands,brand_name FROM CTE1

@saurabhsoni1998

2 жыл бұрын

Great. You missed to add order by in over clause and it won't work in SQL Server if it's empty. -- ROW_NUMBER() OVER(order by (select null))

@deepanshugoyal2531

2 жыл бұрын

It depends on which SQL database you are using

@ankitbansal6

2 жыл бұрын

Good one 👍

difficult to understand

WITH CTE1 AS (SELECT ROW_NUMBER() Over (ORDER BY (select(NULL))) as PRODUCT_ID, category, brand_name FROM dairy..brands), CTE2 AS (SELECT PRODUCT_ID, category, brand_name, COUNT(category) OVER (Order by PRODUCT_ID) as C_group FROM CTE1) SELECT FIRST_VALUE(category) OVER ( Partition by C_group Order by PRODUCT_ID) as CATEGORY, brand_name FROM CTE2

SELECT LAST_VALUE(CATEGORY IGNORE NULLS) OVER (ORDER BY ROWNUM) AS CATEGORY,BRAND_NAME FROM brands;

hi Ankit, Can you please write the sql for this

@rakeshkoll7112

2 жыл бұрын

get the movies played time for each movie (you dont't need to consider pause to resume)

@ankitbansal6

2 жыл бұрын

Some data please

@rakeshkoll7112

2 жыл бұрын

@@ankitbansal6 USE [Chinook] GO /****** Object: Table [dbo].[movies_playback] Script Date: 7/15/2022 9:37:15 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[movies_playback]( [device_timestamp] [datetime] NULL, [session] [varchar](50) NULL, [name] [varchar](50) NULL, [action] [varchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:00:00.000' AS DateTime), N'1', N'movie1', N'start') GO INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:20:00.000' AS DateTime), N'1', N'movie1', N'pause') GO INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:30:00.000' AS DateTime), N'1', N'movie1', N'resume') GO INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T12:42:00.000' AS DateTime), N'1', N'movie1', N'stop') GO INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T13:00:00.000' AS DateTime), N'2', N'movie2', N'start') GO INSERT [dbo].[movies_playback] ([device_timestamp], [session], [name], [action]) VALUES (CAST(N'2022-08-14T13:20:00.000' AS DateTime), N'2', N'movie2', N'stop') GO

First Solution: WITH cte1 AS( SELECT *, SUM(CASE WHEN category IS NOT NULL THEN 1 ELSE 0 END) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS temp FROM brands), cte2 AS( SELECT category, temp FROM cte1 WHERE category IS NOT NULL) SELECT c2.category, c1.brand_name FROM cte1 c1 JOIN cte2 c2 ON c1.temp = c2.temp Second Solution: with cte as (select *, COUNT(category) over (order by rn)as cnt from (select *, row_number() over (order by (select null))rn from brands)a) select first_value(category) over (partition by cnt order by rn)category_new , brand_name from cte

with cte as ( SELECT *, row_number() over() as rn FROM brands ) , cte2 as ( SELECT category,brand_name,rn as start, lead(rn,1,99) over() as last FROM cte where category is not NULL ) , cte3 as ( SELECT b.*, c.category as cat, c.brand_name as brd FROM cte b LEFT JOIN cte2 c ON b.rn > c.start AND b.rn select coalesce(category,cat)as categ, coalesce(brand_name,brd) as brnd from cte3

WITH cte AS( SELECT *, ROW_NUMBER() OVER(ORDER BY NULL) AS rn FROM brands ), cte2 AS( SELECT *, COUNT(category) OVER(order by rn) AS cnt FROM cte ) SELECT MAX(category) OVER(PARTITION BY cnt) AS category, brand_name FROM cte2

Tried without looking into the video...Hope there is a elegant way to do it. select * from fill_null where category is not NULL union all select first.category,second.brand_name from( select category,ROW_NUMBER()over(order by (select null)) as row1 from fill_null where category is not NULL) as first left join ( select category,brand_name,dense_rank()over(order by group_date) as row2 from( select category,brand_name,DATEADD(day,-1*ROW_NUMBER()over(order by(select null)),con_dat) as group_date from (select *,DATEADD(day,ROW_NUMBER()over(order by(select null)),2020-01-01) as con_dat from fill_null) as data where category is NULL) as data1) as second on first.row1=second.row2

@ankitbansal6

2 жыл бұрын

It is difficult to read this query because of so many sub queries. That's why I always suggest to use cte so that it is easy to read through it.

@DineshKumar-hf7ts

2 жыл бұрын

Sure... Will do that from next time 👍

with cte as (select *, row_number() over() as rn from brands) select *, count(case when category is null then 0 else 1 end) over(order by rn) as y from (select first_value(category) over(partition by x order by rn) as category, brand_name,rn,x from (select *, count(category) over(order by rn) as x from cte)a order by rn)b

this qustion for freshers or experienced ones

@ankitbansal6

2 жыл бұрын

Experienced

@vikramcena7131

2 жыл бұрын

@@ankitbansal6 how they will ask for freshers easy or hard please replay bro

@ankitbansal6

2 жыл бұрын

@@vikramcena7131 intermediate

@vikramcena7131

2 жыл бұрын

@@ankitbansal6 thanku so munch bro

@vikramcena7131

2 жыл бұрын

but i havae a doubt bro how will u execute that if your data base in masters

with cte1 as ( SELECT * , ROW_NUMBER()OVER(ORDER BY (select 100)) as rn FROM brands ) SELECT * ,CASE when rn BETWEEN 1 AND 4 THEN (SELECT category from cte1 where rn =1) when rn BETWEEN 5 AND 7 THEN (SELECT category from cte1 where rn =5) END as cat from cte1

@uditnarayanjoshi4115

2 жыл бұрын

Always try to make dynamic query, coz if new data will come with some new categories then u have to write the query again.

@ankitbansal6

2 жыл бұрын

You can not hard code the range

with cte1 as (SELECT *,lag(category) over() prev,row_number() over() rn FROM gdb023.brands) ,cte2 as( select category,brand_name, sum(case when category is not null and prev is null then 1 else 0 end) over(order by rn) summ from cte1) select c1.category,c2.brand_name from cte2 c1 join cte2 c2 using (summ) having category is not null (readable solution!)

Hi Ankit, once again thank you for this amazing question and the solution as well Here's my approach :- WITH cte1 AS( SELECT *, SUM(CASE WHEN category IS NOT NULL THEN 1 ELSE 0 END) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS temp FROM brands), cte2 AS( SELECT category, temp FROM cte1 WHERE category IS NOT NULL) SELECT c2.category, c1.brand_name FROM cte1 c1 JOIN cte2 c2 ON c1.temp = c2.temp

@ankitbansal6

Жыл бұрын

Thanks for posting 👏

Solved it in first attempt. @ankit bansal please have a look and give your valuable feedback with cte1 as( select category,brand_name,lag(category,1,category) over() as prev, row_number() over() as sn from brands ), cte2 as( select sn,category, brand_name, prev, sum(case when prev is NULL and category is not NULL then 1 else 0 end) over(order by sn) as block from cte1 ), cte3 as ( select max(category) over(partition by block) as category, cte2.brand_name from cte2 ) select * from cte3; +------------+------------+ | category | brand_name | +------------+------------+ | chocolates | 5-star | | chocolates | dairy milk | | chocolates | perk | | chocolates | eclair | | Biscuits | britannia | | Biscuits | good day | | Biscuits | boost | +------------+------------+

Hey this is really nice question. @ankitbansal

with cte1 as ( select row_number() over(order by (select null)) as rn, * from brands ) , cte2 as ( select * , count(category) over(order by rn) as cnt from cte1 ) select FIRST_VALUE(category) over(partition by cnt order by rn ) as category , brand_name from cte2