Видео

WITH sales_data AS ( SELECT dt, sales, LAG(sales, 1, 0) OVER (ORDER BY dt) AS PrevDaySales, CASE WHEN LAG(sales, 1, 0) OVER (ORDER BY dt) = 0 THEN NULL -- To avoid division by zero ELSE (sales - LAG(sales, 1, 0) OVER (ORDER BY dt)) * 100.0 / LAG(sales, 1, 0) OVER (ORDER BY dt) END AS var_pct FROM practiceDB1.inputSales ORDER BY dt ) SELECT dt AS Business_Date, sales, COALESCE(CAST(ROUND(var_pct,0) AS STRING),'') AS `%var` FROM sales_data WHERE COALESCE(CAST(ROUND(var_pct,0) AS STRING),'') NOT LIKE '-%';

with cte as( select sname,marks, ROW_NUMBER() over ( partition by sname order by marks desc)as d_rn from students) select sname,SUM(marks)as total_marks from cte where d_rn in (1,2) group by sname;

Thanks you

Using lag and case: WITH temp_table AS ( SELECT category, brand_name, LAG(category) OVER (ORDER BY category, brand_name) AS prev_category FROM snacks ) SELECT (CASE WHEN category IS NULL THEN prev_category ELSE category END) AS category, brand_name FROM temp_table;

SELECT *, STUFF(card_number, 1, 12, REPLICATE('*', 12)) AS masked_number FROM Cards;

df.filter( col('empid')==df.groupBy('empid').count().filter(col('count')==1).select('empid').collect()[0][0]).show()

U must mistakenly order by empid instead of salary .to get min salary..

With cte_main ( select *, row_number ( ) over(partition by cust_id)rn from flights), Cte_source as ( select cust_id,origin from cte_main where rn=1), Cte_dest as ( select cust_id, destination from.cte_main where rn=2) Select s.cust_id,s.origin,t.destination from cte_source s,cte_dest t where s.cust_id=t.cust_id

select *,sum(case when transaction_type='deposit' then amount else -amount end)over(order by transaction_date rows between unbounded preceding and current row)as rolling_total from transactions;

WITH CTE AS( SELECT *, ROW_NUMBER() OVER(partition by MONTH(DATE),CURRENCY_CODE ORDER BY MONTH(DATE)) DD FROM mall.exchange_rates), J AS ( SELECT *, MIN(DD) OVER(partition by MONTH(DATE),CURRENCY_CODE ORDER BY MONTH(DATE) ) MI, MAX(DD) OVER(partition by MONTH(DATE),CURRENCY_CODE ORDER BY MONTH(DATE) ) MA FROM CTE) SELECT currency_code,date,currency_exchange_rate FROM j WHERE dd=mi or dd=ma order by currency_code;

Alternate Approach --Unpivot Data with PVOT function WITH unpivot_data AS ( SELECT col1, attribute, value FROM practiceDB1.tbl_maxval UNPIVOT ( value FOR attribute IN (col2, col3) ) ) SELECT col1, MAX(value) AS maxval FROM unpivot_data GROUP BY col1 ORDER BY col1;

with cte AS (select salary,count(salary) from Employee group by salary having count(salary) > 1 ) select ename from Employee where salary in (select salary from cte)

Your approach for the solution is great sir !!

with testtbl_cte as( select unnest(string_to_array(cola,',')) as new_col from testtbl), second_part as(select t1.new_col as first,t2.new_col as second from testtbl_cte t1 cross join testtbl_cte t2 where t1.new_col != t2.new_col) select * from second_part where first> second

sir can you tell which topics we need to prepare for round 1 assesment

how can ffind 3 consecutive seat

With cte as(Select * , ISNULL(LAG(ytd_sales) over(order by ytd_sales),0) as Id from sales) select month,ytd_sales, ytd_sales - ID As periodicsales from cte

With cte as(SELECT * from table_a UNION Select * from table_b), cte2 as(Select *, Rank() over (partition by empid order by salary) as rn from cte) select empid,empname,salary from cte2 where rn < 2

WITH GenderCounts AS ( SELECT SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS MaleCount, SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleCount, COUNT(*) AS TotalCount FROM employee ) SELECT (MaleCount * 100.0) / TotalCount AS MalePercentage, (FemaleCount * 100.0) / TotalCount AS FemalePercentage FROM GenderCounts;

WITH CTE AS( SELECT DAYNAME(ID) DN,DATE(ID) D,TIME(ID) T,EMPID FROM emp_tbl ORDER BY EMPID,ID) , J AS( SELECT *, LAG(T,1) OVER(PARTITION BY D ) win FROM CTE WHERE DN IN ('SATURDAY','SUNDAY')), K AS( SELECT EMPID,D,T,TIMESTAMPDIFF(HOUR,WIN,T) HOURS FROM J) SELECT EMPID,SUM(HOURS) H FROM K GROUP BY EMPID ORDER BY EMPID;

WITH CTE AS( SELECT SLS_DT,adddate(SLS_DT , INTERVAL +7 DAY) week FROM SLS_TBL), J AS( SELECT *, LAG(WEEK,1) OVER(ORDER BY WEEK) N FROM CTE) SELECT N FROM J WHERE SLS_DT!=N;

First we can apply disticnt id and then get a max value

WITH cte AS( SELECT * ROW_NUMBER() OVER(PARTITION BY sname ORDER BY marks DESC) AS rn FROM Student) SELECT sname, max(rn) FROM cte GROUP BY sname

%%sql with cte as ( select gender, count(1) as gender_count from employees_tbl group by gender ) select gender, gender_count, cast((gender_count * 100) / sum(gender_count) over() as int) as gender_percentage FROM cte;

with cte as( select *, lead(destination ,1) over(partition by cust_id order by cust_id) final_destination from flights), l as( select *, row_number() over(partition by cust_id order by cust_id) s from cte) select cust_id,origin,final_destination from l where s=1;

select col1,(length(col2)-length(replace(col2,',','')))+1 as count1 from tbl_cnt;

with cte as (select brand_name, case when brand_name='dairy milk' then 'chocolate' when brand_name='perk' then 'chocolate' when brand_name='eclair' then 'chocolate' when brand_name='good day' then 'biscuits' when brand_name='boost' then 'biscuits' else category end category from brands) select category,brand_name from cte;

select sname,marks, case when marks<35 then 'poor' when marks>=35 and marks<=60 then 'good' when marks>60 and marks <=80 then 'vgood' else 'excellent' end as grade from st;

select * from happiness_tbl ORDER BY CASE country WHEN 'India' THEN 0 WHEN 'Sri Lanka' THEN 1 ELSE 2 END ;

( with a as( select *, row_number() over(order by ranking desc) r from happiness_tbl limit 2) select ranking,country from a order by ranking) union select * from happiness_tbl ORDER BY CASE country WHEN 'India' THEN 1 WHEN 'Sri Lanka' THEN 2 ELSE 3 END ;

with cte as( select *, lag(ytd_sales,1) over(order by monthnum) d from sales), j as( select month, ytd_sales,(ytd_sales-d) as periodic from cte order by monthnum) select month,ytd_sales, case when periodic is null then ytd_sales else periodic end as periodicsales from j;

with cte as ( select * from tablea union all select * from tableb), f as( select *, row_number() over(partition by empname) as d from cte) select empid,empname,salary from f where d=1;

WITH CTE AS( SELECT *, COUNT(*) OVER (PARTITION BY ID ORDER BY ID ) F FROM S) SELECT MAX(ID) MAXIMUM_ID FROM CTE WHERE F=1;

select max(emp_id) maxemp from (select emp_id,count(emp_id) from em group by emp_id having count(emp_id)=1)

THIS CAN BE A DYNAMIC APPROACH TOO with cte as (select *,row_number() over(order by seat_id) as rn, seat_id - row_number() over(order by seat_id) as diff from cinema_tbl where free<>0), t2 as ( select *, count(diff) over(partition by diff) as cnt from cte) select seat_id from t2 where cnt>1

select top 1(id) from employee group by id having count(id)=1 order by id desc

Select card_number,lpad(substr(card_number,-4), length (card_number),'*')as New _card_number from table

By using case statement. Select case when to_ char(trunc(sysdate,'mm'),'d') =4 then trunc(sysdate,'mm')+7 else next_day(next_day(trunc(sysdate, 'mm'),'wed'),'wed') end as 2-nd - Wednesday from dual;

Select top 1 id from employees Group by id having count(*) = 1 order by id desc

with cte as (select *,rank() over(partition by dept_id order by salary desc) as rnk from emps_tbl) select dept_id, max(case when rnk =1 then emp_name end) as max_sal_emp, min(case when rnk >1 then emp_name end) as max_sal_emp from cte where rnk<3 group by 1

with cte as (select country from happiness_tbl where ranking>100 order by ranking), cte2 as ( select * from cte union all select country from happiness_tbl where ranking<100), cte3 as( select *,row_number() over() as rn from cte2) select case when rn= 2 then lag(country) over() when rn =1 then lead(country) over() else country end as flag from cte3

Left outer join

with CTE as ( select id , count(*) as count_ from employees group by id having count(*)!>1) select max(id) as max_id from CTE;

with cte1 as( select *, ROW_NUMBER() over(partition by sname order by marks desc) as rnum from students) select cte1.sname,sum(marks) as total_marks from cte1 where cte1.rnum<=2 group by cte1.sname;

;with cte_val as( select concat_ws('_',currency_code,year(date),month(date)) as currency_code, min(currency_exchange_rate) over(partition by currency_code,date ) as current_start, max(currency_exchange_rate) over(partition by currency_code,date ) as currency_end from exchange_rates) select currency_code,min(current_start) as start_val,max(currency_end) as end_val from cte_val --where currency_code like 'USD%' group by currency_code

With cte as ( Select Dateadd(week,1,Sls_dt) as sls_dt from sls_tbl ) Select * from cte Where sls_dt not in (Select sls_dt from sls_tbl) and sls_dt < (Select Max(sls_dt) from sls_tbl)

WITH DeptMaxScores AS ( SELECT dept, MAX(scores) AS max_score FROM empdept_tbl GROUP BY dept ) UPDATE empdept_tbl SET scores = d.max_score FROM empdept_tbl e JOIN DeptMaxScores d ON e.dept = d.dept;

with cte as( select *, row_number() over(partition by id order by id) rn from employees ) select id from cte group by 1 having count(*) =1 order by 1 desc limit 1 ;

select e1.ename from employees_tbl e1 right join employees_tbl e2 on e1.managerid = e2.empid where e1.salary > e2.salary

select * from tablea UNION select * from tableb where empid!=2