Is this solution correct we have with cte as ( select *, COUNT(company) over (order by car_id)as aa from vehicle ) select *,FIRST_VALUE(company) over (partition by aa order by aa) from cte
Hi @Data Project Hub what about this code could tell me is this approch is right with cte as( select *,sum(weight_kg) over(partition by lift_id order by weight_kg) as total_sum from l) select lift_id,STRING_AGG(passenger_name,',')as passeneger_list from cte where lift_id=1 and total_sum <300 or lift_id =2 and total_sum <350 group by lift_id
check this out! select lift_id, STRING_AGG(passenger_name, ', ' ORDER BY weight_kg ) AS aggregated_names_2 from ( select * from ( select *, sum(weight_kg) over(partition by lift_id order by weight_kg) as total_W from ( Select * from lift_passengers as L1 join lift as L2 on L1.Lift_id = L2.id)x)y where capacity_kg > total_W) z group by lift_id 😉
Thank you for sharing! here's my attempt on SQL server: ================================== with max_min as (select * from (select *, MAX(score) over(partition by exam_id order by exam_id) [Max score], MIN(score) over(partition by exam_id order by exam_id) [Min score] from exam) s1 where case when score > [Min score] and score < [Max score] then 'Y' else 'N' end = 'Y'), exam_no as (select student_id, COUNT(student_id) [num exams] from exam group by student_id) select distinct s.student_id, student_name from max_min m join exam_no e on m.student_id = e.student_id join student s on s.student_id = m.student_id where [num exams] >= 1 ==================================
;WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (partition by customer_id order by order_date) rn ,LAG(status,1) OVER(partition by customer_id order by order_date) lg_id ,LAG(order_date,1) OVER(partition by customer_id order by order_date) lg_id1 FROM customer_orders ) SELECT customer_id,DATEDIFF(DAY,lg_id1,order_date) as DaysTook FROM CTE WHERE lg_id IS NOT NULL and lg_id1 is not null and ((status='Delivered' and lg_id='Shipped')) and lg_id1<order_date
My Solution. WITH CTE AS (SELECT c.customer_id, c.customer_name, o.order_id, o.order_date, o.order_amount, MIN(order_date) OVER(PARTITION BY o.customer_id ORDER BY c.customer_id) AS first_order_date FROM customers c JOIN order_details o ON c.customer_id = o.customer_id) SELECT TOP 3 customer_id, customer_name, SUM(order_amount) AS total_amount FROM CTE WHERE month(order_date) = month(first_order_date) GROUP BY customer_id, customer_name ORDER BY total_amount DESC
select distinct item from (select * , dense_rank() over (partition by item order by id ) as dn from items_table) x where dn >=3 i am getting the same output, is this correct method? please let me know
Thanks for the excellent explanation. I solved the same using this query: with cte as (select *, day(CreatedAt) as DAY_NO, coalesce(lag(day(CreatedAt)) over(partition by userId order by CreatedAt),0) as prev_DAY from Transactions_Ecomm), cte1 as (select *, case when (DAY_NO-prev_DAY)<=7 AND prev_DAY <> 0 AND DAY_NO <> prev_DAY then userId else null end as USERID_DIFFERENCE_IN_DAYS from cte) select DISTINCT userId from cte1 where userId=USERID_DIFFERENCE_IN_DAYS;
If I'm not wrong, isn't this a simpler way to solve this: WITH cte as (select *,LEAST(English,Maths,Science,Geography,History,Sanskrit) as LEAST_MARKS from exam) select *, (English+Maths+Science+Geography+History+Sanskrit-LEAST_MARKS)/5 as Output_Marks from cte;
with cte as( select user_id_send,user_id_receiver_id , max(case when action='sent' then date end )as sent_date , max(case when action='recevied' then date end) as received_date from fb_friend_requests group by user_id_send,user_id_receiver_id) select sent_date ,count(sent_date) ,count(received_date )*1.0 / count(sent_date) *100 as percent_rate from cte group by sent_date
Bro atleast provide create & insert statement for the question you have discussed. No doubt , every time you bring valuable sql questions for the subscribers . But don't forget to add the create & insert statement in the description box.
MY Solution in MSSQL : Select * from Students; with cte1 as ( Select * ,lead(Student_Name,1,Student_Name)over(order by ID) as rn_lead ,lag(Student_Name,1,Student_Name)over(order by ID) as rn_lag from Students ) Select ID , case when id%2=0 then rn_lag else rn_lead end as new from cte1
thanks for the valuable question. I used your logic only. with r_cte as ( select char(65) as letter union all select char(ASCII(letter)+1) as letter from r_cte where letter < 'Z' ),n_cte as ( select 1 as num union all select num+1 as num from n_cte where num< 10 ),cte3 as ( Select letter, num, CONCAT(letter,num) as seat_id from n_cte cross join r_cte ) Select letter, STRING_AGG(seat_id,', ') as seat_id from cte3 group by letter
Select * ,first_value(sales)over(partition by product order by id) as rn ,sum(sales)over(partition by product order by id) as rinning_total_sales from sales_tab order by id,product;
with cte as( select *, lag(item,1) over(order by id ) laag, lead(item,1) over(order by id) leaad from items_table),cte2 as ( select *,case when item=laag then 1 when item= leaad then 1 else 0 end as flag from cte),cte3 as ( select distinct(item),flag,count(flag) over(partition by item) as cnt from cte2 where flag=1) select item from cte3 where cnt>=3
Another approach WITH cte AS ( SELECT *, COUNT(voter) OVER (PARTITION BY voter) AS voter_cnt, ROUND(1.0 / COUNT(voter) OVER (PARTITION BY voter), 3) AS vote_value FROM votes ) SELECT candidate,sum(vote_value) as total_votes from cte where candidate is not null group by candidate order by sum(vote_value) desc limit 1;
Here is my answer - can you tell me what you think of it with cte as ( SELECT emp_id, name, POWER(10,(5 - LENGTH(CAST(salary AS CHAR(50))))) * salary as newsal FROM emp_sal) select emp_id, name, newsal, avg(newsal) over() as avgsal from cte;
Thanks for explanation: I used your logic and solve little different method with rcte as ( Select min(start_date) as Date , max(end_date) as end_date from Tasks union all Select dateadd(day,1,Date) as Date, end_date from rcte where Date<end_date ) Select Date from rcte option(maxrecursion 3000);
Thanks for the explanation. ------------------------------ I used your method without cte Select value as words,count(*) as no_of_accurence from google_file_store cross apply string_split(contents,' ') where filename like 'draft%' group by value order by value
My solution in MSSQL DB: with cte1 as ( Select * ,row_number()over(partition by [group] order by sequence) as rn ,sequence - row_number()over(partition by [group] order by sequence) as rn1 from tab_sequence ) Select [group],min(sequence) as min_seq, max(sequence) as ma_seq from cte1 group by [group], rn1 order by [group]
My version of solution. WITH CTE AS (SELECT *, FIRST_VALUE(destination_phone_nbr) OVER(PARTITION BY source_phone_nbr ORDER BY source_phone_nbr) AS first_dest, LAST_VALUE(destination_phone_nbr) OVER(PARTITION BY source_phone_nbr ORDER BY source_phone_nbr) AS last_dest FROM phone_log) SELECT DISTINCT source_phone_nbr, CASE WHEN first_dest = last_dest THEN 'Y' ELSE 'N' END AS flag FROM CTE
Hi with cte as( select source_phone_nbr, destination_phone_nbr from phone_log where call_start_date_time=(select min(call_start_date_time) from phone_log ) ), cte2 as ( select source_phone_nbr, destination_phone_nbr from phone_log where call_start_date_time=(select max(call_start_date_time) from phone_log )), cte3 as( select * from cte union all select * from cte2) select source_phone_nbr ,case when count(*) >1 then 'y' ELSE 'n' END AS flag from cte3 group by source_phone_nbr ;
