can you please tell me the error in this code with cte as ( select year, round(avg(height),2) as avght from athletes where sex='M' and year between 1896 and 2016 group by year order by year asc) select year,avght, lag(avght,1,172.73) over() as prev_yr_avght, round(abs(avght-lag(avght,1,172.73) over()),2) as diff from cte ; year avght prev_yr_avght diff 2016 181.68 178 3.68 2012 178 184 6 2008 184 178 6 1996 178 182.5 4.5 1994 182.5 179 3.5 1992 179 175 4 1984 175 175.28 0.28 1924 175.28 179.25 3.97 1908 179.25 165 14.25 1904 165 174.5 9.5 1900 174.5 172.73 1.77
This is my attempt in MYSQL with cte as ( select week(invoicedate) as week_num,weekday(invoicedate) as wk_day, dayname(invoicedate) as dayname, sum(quantity*unitprice) as ttl_sale from early_sales group by 1,2,3 order by 1,2 ), cte2 as ( select week_num,sum(ttl_sale) as total_sale from cte group by 1), cte3 as ( select week_num,ttl_sale,dayname, first_value(ttl_sale) over(partition by week_num order by wk_day) as monday_value , last_value(ttl_sale) over(partition by week_num order by wk_day rows between unbounded preceding and unbounded following) as sunday_value, count(*) over(partition by week_num) as cnt from cte where dayname in ("sunday","monday")), cte4 as ( select distinct week_num,if(cnt=1 and dayname="monday",sunday_value=0,sunday_value) as sunday_value, if(cnt=1 and dayname="sunday",monday_value=0,monday_value) as monday_value from cte3) select cte4.week_num as week_num, round((monday_value/total_sale)*100,0) as percent_monday_sale, round((sunday_value/total_sale)*100,0) as percent_sunday_sale from cte4 inner join cte2 on cte4.week_num=cte2.week_num
another approach with cte as ( select state,cust_id,timestamp as start_time,lead(timestamp,1) over(partition by cust_id) as endtime from customer_activity),cte2 as ( select cust_id,timestampdiff(second,start_time,endtime)/3600 as hrs_present from cte where state=1) select distinct(cust_id),sum(hrs_present) over(partition by cust_id) as hrs from cte2;
another approach with cte as( select *,row_number() over(partition by user_id order by watched) as rnm from watch),cte2 as ( select *,count(video_id) over(partition by video_id) as cnt from cte where rnm<=3),cte3 as ( select distinct(video_id),count(cnt) as cnt from cte2 group by video_id order by count(cnt) desc),cte4 as( select *,dense_rank() over(order by cnt desc) rnk from cte3 ) select video_id,cnt from cte4 where rnk<=3;
Yes, the mathematical formula of median for odd nos. Is (n+1)/2 but when we implement the logic of median in sql, as you can see we've used the round function which does the same thing. For an instance, Here n = 135 n/2 = 67.2 Round (n/2) = 68 And when we implement the same in mathematical formula it will be (n+1)/2, which is (135+1)/2 = 136/2 = 68. Both of these will give the same result. Hope this answers your question.
