SQLize Online / PHPize Online  /  SQLtest Online

-- find the cumulative percentage of overall sale CREATE TABLE Sales ( Region NVARCHAR(50), Order_Date DATE, Order_ID INT, Amount INT); INSERT INTO Sales (Region, Order_Date, Order_ID, Amount) VALUES ('North', '2024-01-15', 1, 500), ('North', '2024-03-05', 5, 600), ('South', '2024-01-20', 2, 700), ('East', '2024-02-18', 4, 200); with cte as( select *, cast((1.00*sum(amount) over(partition by region order by order_date asc rows between unbounded preceding and current row ) /sum(amount) over(partition by region) )as decimal (10,2) )cum_sum from sales) select * from cte order by region,order_date; --find the first and last order of the each cutomer CREATE TABLE CustomerOrders ( Order_ID INT, Customer_ID INT, Order_Date DATE, Amount INT ); INSERT INTO CustomerOrders (Order_ID, Customer_ID, Order_Date, Amount) VALUES (1, 101, '2024-01-15', 500), (2, 102, '2024-01-20', 700), (3, 101, '2024-02-10', 300), (4, 103, '2024-02-18', 200), (5, 101, '2024-03-05', 600); with cte as(select *,row_number () over (partition by customer_id order by order_date asc ) rn1, row_number ()over (partition by customer_id order by order_date desc ) rn2 from customerorders ) select Order_ID, Customer_ID, Order_Date, Amount from cte where rn1 =1 or rn2=1; -- CREATE TABLE RollingSales ( Region NVARCHAR(50), Months NVARCHAR(50), Year INT, Sales INT ); INSERT INTO RollingSales (Region, Months, Year, Sales) VALUES ('North', 'January', 2024, 15000), ('North', 'February', 2024, 18000), ('North', 'March', 2024, 20000), ('South', 'January', 2024, 14000), ('South', 'February', 2025, 16000); --create calender table --join then use cummulative sum for 3 period /* with cte as( select top 1 datename(month,datefromparts(2024,1,1)) dates,1 rn, 0 flag from RollingSales union all select datename(month,datefromparts(2024,rn+1,1)),rn+1 ,rn/3 from cte where rn<=11 ) select * from cte;*/ with cte as ( select datefromparts( year,01,01) dates ,1 as rn from rollingsales group by year union all select dateadd(month,1,dates) ,rn+1 from cte where rn<=11 ) select * from cte; --find the no of candidate for each experience and how many of them achived maximum no of marks(100) --Note Null=candidate not participated in that challenge create table assessments ( id int, experience int, sql int, algo int, bug_fixing int ) insert into assessments values (1,3,100,null,50), (2,5,null,100,100), (3,1,100,100,100), (4,5,100,50,null), (5,5,100,100,100); --(5,5,100,100,100); select * from assessments; select experience ,count(distinct id ) no_of_cndidate ,sum( case when (coalesce(sql,0)+coalesce(algo,0)+coalesce(bug_fixing,0))/( case when sql =100 then 1 else 0 end + case when algo =100 then 1 else 0 end+ case when bug_fixing =100 then 1 else 0 end)=100 then 1 else 0 end) maximum_scored from assessments group by experience ; select * , case when sql=100 then 1 else null end , case when algo =100 then 1 else null end, case when bug_fixing=100 then 1 else null end from assessments; select * , case when sql <100 then null else 1 end from assessments; select 100<null /*delete from assessments insert into assessments values (1,2,null,null,null), (2,20,null,null,20), (3,7,100,null,100), (4,3,100,50,null), (5,2,40,100,100);