SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
-- 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

Stuck with a problem? Got Error? Ask ChatGPT!

Copy Clear