SQLize Online / PHPize Online  /  SQLtest Online

/*CREATE TABLE swipe ( employee_id INT, activity_type VARCHAR(10), activity_time datetime ); -- Insert sample data INSERT INTO swipe (employee_id, activity_type, activity_time) VALUES (1, 'login', '2024-07-23 08:00:00​'), (1, 'logout', '2024-07-23 12:00:00​'), (1, 'login', '2024-07-23 13:00:00​'), (1, 'logout', '2024-07-23 17:00:00​'), (2, 'login', '2024-07-23 09:00:00​'), (2, 'logout', '2024-07-23 11:00:00​'), (2, 'login', '2024-07-23 12:00:00​'), (2, 'logout', '2024-07-23 15:00:00​'), (1, 'login', '2024-07-24 08:30:00​'), (1, 'logout', '2024-07-24 12:30:00​'), (2, 'login', '2024-07-24 09:30:00​'), (2, 'logout', '2024-07-24 10:30:00​'); select * from swipe; --select lead() /* --cumulative sum without using windows function create table tab( val int); insert into tab values(100), (200) ,(300) ,(400), (500) ; with cte as( select val, (select count(val) from tab t where t.val<=t1.val)rnk from tab t1) , cte2 as( select val, rnk, val cum_sum from cte where rnk=1 union all select cte.val, cte.rnk, cte.val+cte2.cum_sum from cte inner join cte2 on cte2.rnk+1=cte.rnk) select * from cte2; */ create table logs( log_id int); insert into logs values (1),(2) ,(2),(3), (7),(8),(10); /* select min(log_id) start_id,max(log_id) end_id from (select *, log_id-dense_rank() over(order by log_id) grp_key from logs) k group by grp_key order by start_id ; */ /* with cte as( select *,dense_rank() over(order by a) r from t) , cte2 as( select a,r, 1 cnt from cte where r=1 union all select cte.a, cte.r, case when cte.a-cte2.a =1 then cte2.cnt+1 else cte2.cnt end as cnt from cte2 inner join cte on cte.r-cte2.r=1) --and cte2.a-cte.a=1) select * from cte2; */ CREATE TABLE sales ( date_sold DATE, product NVARCHAR(50), amount_sold INT ); INSERT INTO sales (date_sold, product, amount_sold) VALUES ('2022-06-01', 'Cake', 6), ('2022-06-01', 'Pie', 18), ('2022-06-02', 'Pie', 3), ('2022-06-02', 'Cake', 2), ('2022-06-03', 'Pie', 14), ('2022-06-03', 'Cake', 15), ('2022-06-04', 'Pie', 15), ('2022-06-04', 'Cake', 6), ('2022-06-05', 'Cake', 16), ('2022-06-05', 'Pie',null); with cte as(select date_sold,product,sum(amount_sold)total from sales group by date_sold,product) select * from cte select a.date_sold, abs(a.total-b.total) difference , case when a.total>b.total then a.product when a.total<b.total then b.product else a.product+',' +b.product end product from cte a inner join cte b on a.date_sold=b.date_sold and a.product='cake' and b.product='pie'; with cte1 as( select date_sold, sum(iif(product='pie', amount_sold, 0)) pie_sold, sum(iif(product='cake', amount_sold, 0)) cake_sold from sales group by date_sold) select date_sold, abs(pie_sold-cake_sold) , case when pie_sold>cake_sold them 'pie' when pie_sold<cake_sold then 'cake' else 'tie' end max_sold_product