/*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 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) difference ,
case
when pie_sold>cake_sold then 'pie'
when pie_sold<cake_sold then 'cake'
else 'tie'
end max_sold_product
from cte1;
Telegram: DB developers channel