SQLize Online / PHPize Online  /  SQLtest Online

create table parent_child_status ( parent_id int, child_id int, status varchar(20) ); insert into parent_child_status values (1, 3, 'Active'); insert into parent_child_status values (1, 4, 'InActive'); insert into parent_child_status values (1, 5, 'Active'); insert into parent_child_status values (1, 6, 'InActive'); insert into parent_child_status values (2, 7, 'Active'); insert into parent_child_status values (2, 8, 'InActive'); insert into parent_child_status values (3, 9, 'Inactive'); insert into parent_child_status values (4, 10, 'Inactive'); insert into parent_child_status values (4, 11, 'Active'); insert into parent_child_status values (5, 12, 'InActive'); insert into parent_child_status values (5, 13, 'InActive'); --1 select distinct parent_id, 'active' status from parent_child_status where status ='active' union all select distinct parent_id, 'inactive' status from parent_child_status where parent_id not in (select distinct parent_id from parent_child_status where status ='active') order by 1; --2 select parent_id, iif(cnt>0, 'active','inactive') status from( select parent_id, sum( iif(status='active',1,0) ) cnt from parent_child_status group by parent_id) k; -- Create the Orders table CREATE TABLE namaste_orders ( order_id INT, city VARCHAR(10), sales INT ); -- Create the Returns table CREATE TABLE namaste_returns ( order_id INT, return_reason VARCHAR(20) ); -- Insert data into Orders table INSERT INTO namaste_orders VALUES (1, 'Mysore', 100), (2, 'Mysore', 200), (3, 'Bangalore', 250), (4, 'Bangalore', 150), (5, 'Mumbai', 300), (6, 'Mumbai', 500), (7, 'Mumbai', 800); -- Insert data into Returns table INSERT INTO namaste_returns VALUES (3, 'wrong item'), (6, 'bad quality'), (7, 'wrong item'); -- left join on order id, namaste_return --left join with namaste_orders to 'join_table's city after faltering namaste_returns id is null select t3.* from namaste_orders t3 left join (select distinct (t1.city) city from Namaste_orders t1 left join namaste_returns t2 on t2.order_id=t1.order_id where t2.order_id is not null) t4 on t3.city=t4.city where t4.city is null ; select t1.city from namaste_orders t1 left join namaste_returns t2 on t2.order_id=t1.order_id --where t2.order_id is not null group by t1.city having sum(t2.order_id) is null ; -- Visits table CREATE TABLE Visits ( user_id INT, visit_date DATE ); -- Inserting data into Visits table INSERT INTO Visits (user_id, visit_date) VALUES (1, '2020-01-01'), (2, '2020-01-02'), (12, '2020-01-01'), (19, '2020-01-03'), (1, '2020-01-02'), (2, '2020-01-03'), (1, '2020-01-04'), (7, '2020-01-11'), (9, '2020-01-25'), (8, '2020-01-28'); -- Transactions table CREATE TABLE Transactions ( user_id INT, transaction_date DATE, amount INT ); -- Inserting data into Transactions table INSERT INTO Transactions (user_id, transaction_date, amount) VALUES (1, '2020-01-02', 120), (2, '2020-01-03', 22), (7, '2020-01-11', 232), (1, '2020-01-04', 7), (9, '2020-01-25', 33), (9, '2020-01-25', 66), (8, '2020-01-28', 1), --(9, '2020-01-25', 99), (9, '2020-01-25', 99); with cte as( select t1.*, t2.user_id id, t2.transaction_date from visits t1 left join transactions t2 on t1.user_id =t2.user_id and t2.transaction_date=t1.visit_date) ,cte2 as(select *, count ( id) over(partition by transaction_date ) t_count from cte), cte3 as( select t_count, count(distinct coalesce (day(transaction_date) , user_id)) no_visits from cte2 group by t_count) , cte4 as( select 0 as n, max(t_count) s from cte3 union all select n+1 , s from cte4 where n<s) select n transactions, coalesce(no_visits,0) from cte4 left join cte3 on cte3.t_count=cte4.n; -- * For transactions_count = 0, The visits (1, "2020-01-01"), (2, "2020-01-02"), (12, "2020-01-01") and (19, "2020-01-03") did no transactions so visits_count = 4. -- * For transactions_count = 1, The visits (2, "2020-01-03"), (7, "2020-01-11"), (8, "2020-01-28"), (1, "2020-01-02") and (1, "2020-01-04") did one transaction so visits_count = 5. -- * For transactions_count = 2, No customers visited the bank and did two transactions so visits_count = 0. -- * For transactions_count = 3, The visit (9, "2020-01-25") did three transactions so visits_count = 1. -- * For transactions_count >= 4, No customers visited the bank and did more than three transactions so we will stop at transactions_count = 3 -------------------+--------------+ -- | transactions_count | visits_count | -- +--------------------+--------------+ -- | 0 | 4 | -- | 1 | 5 | -- | 2 | 0 | -- | 3 | 1 | -- +--------------------+--------------+ with cte2 as( select t1.*, t2.user_id id, t2.transaction_date,count ( t2.user_id) over(partition by t2.transaction_date ) t_count from visits t1 left join transactions t2 on t1.user_id =t2.user_id and t2.transaction_date=t1.visit_date) , cte3 as( select t_count, count(distinct coalesce (day(transaction_date) , user_id)) no_visits from cte2 group by t_count) , cte4 as( select 0 as n, max(t_count) s from cte3 union all select n+1 , s from cte4 where n<s) select n transactions, coalesce(no_visits,0) from cte4 left join cte3 on cte3.t_count=cte4.n; WITH TransactionCounts AS ( SELECT user_id, COUNT(*) AS transactions_count FROM Transactions GROUP BY user_id ), VisitCounts AS ( SELECT transactions_count, COUNT(*) AS visits_count FROM TransactionCounts GROUP BY transactions_count ) SELECT DISTINCT tc.transactions_count, COALESCE(vc.visits_count, 0) AS visits_count FROM TransactionCounts tc LEFT JOIN VisitCounts vc ON tc.transactions_count = vc.transactions_count ORDER BY tc.transactions_count; --retrive each month first day price CREATE TABLE sku ( sku_id INT, price_date DATE, price INT ); INSERT INTO sku VALUES (1, '2023-01-01', 10), (1, '2023-02-01', 15), (1, '2023-03-01', 18), (1, '2023-03-27', 15), (1, '2023-04-06', 20); select * from sku; with cte as( select sku_id, dateadd(day, 1,EOmonth(price_date)) price_date ,price,rank() over(partition by year(price_date),month(price_date) order by price_date desc) rn from sku union all select sku_id, price_date,price, 1 from sku where month(price_date)=1 ) select sku_id, price_date, price from cte where rn=1 order by price_date; -- Create the travel_data table CREATE TABLE travel_data ( customer VARCHAR(10), start_loc VARCHAR(50), end_loc VARCHAR(50) ); -- Insert data into the travel_data table INSERT INTO travel_data (customer, start_loc, end_loc) VALUES ('c1', 'New York', 'Lima'), ('c1', 'London', 'New York'), ('c1', 'Lima', 'Sao Paulo'), ('c1', 'Sao Paulo', 'New Delhi'), ('c2', 'Mumbai', 'Hyderabad'), ('c2', 'Surat', 'Pune'), ('c2', 'Hyderabad', 'Surat'), ('c3', 'Kochi', 'Kurnool'), ('c3', 'Lucknow', 'Agra'), ('c3', 'Agra', 'Jaipur'), ('c3', 'Jaipur', 'Kochi'); with cte as( select *, count(*) over(partition by customer ) su from travel_data) ,st as ( select cte.customer,cte.start_loc, cte.su from cte left join cte c on cte.customer=c.customer and cte.start_loc=c.end_loc where c.customer is null) , ed as (select cte.customer,cte.end_loc, cte.su from cte left join cte c on cte.customer=c.customer and cte.end_loc =c.start_loc where c.customer is null) select st.customer, st.start_loc,ed.end_loc ,st.su+1 as cnt from St inner join ed on st.customer=ed.customer; with cte as(select * from (select customer, start_loc, 'start_loc'as typ from travel_data union all select customer, end_loc, 'end_loc'as typ from travel_data) tbl) select customer, start_loc,count(start_loc) s from cte group by customer, start_loc; --more than 2 consecutive order CREATE TABLE purchases (purchase_id INT, customer_id INT, purchase_date DATE ); INSERT INTO purchases VALUES (1, 101, '2024-01-01'); INSERT INTO purchases VALUES (2, 102, '2024-01-02'); INSERT INTO purchases VALUES (3, 101, '2024-01-02'); INSERT INTO purchases VALUES (4, 103, '2024-01-03'); INSERT INTO purchases VALUES (5, 101, '2024-01-03'); INSERT INTO purchases VALUES (6, 104, '2024-01-04'); INSERT INTO purchases VALUES (7, 102, '2024-01-04'); INSERT INTO purchases VALUES (8, 103, '2024-01-05'); INSERT INTO purchases VALUES (9, 102, '2024-01-05'); INSERT INTO purchases VALUES (10, 103, '2024-01-06'); INSERT INTO purchases VALUES (11, 102, '2024-01-06'); INSERT INTO purchases VALUES (12, 107, '2024-01-07'); select * from purchases; with cte as(select *, (dense_rank() over(partition by customer_id order by purchase_date )-day(purchase_date)) k from purchases) select customer_id from cte group by k, customer_id having count(k) >=3; --max amount of money spend by each monthl CREATE TABLE customer ( customer_id INT, cust_name VARCHAR(255), city VARCHAR(255), gender CHAR(1) ); INSERT INTO customer VALUES (3006, 'Geoff Cameron', 'London', 'M'); INSERT INTO customer VALUES (3005, 'Graham Zusi', 'London', 'F'); INSERT INTO customer VALUES (3004, 'Julian Green', 'London', 'M'); INSERT INTO customer VALUES (3003, 'Jozy Altidor', 'Bangalore', 'F'); INSERT INTO customer VALUES (3002, 'Nick Rimando', 'Bangalore', 'M'); INSERT INTO customer VALUES (3001, 'Brad Guzan', 'Bangalore', 'F'); CREATE TABLE orderdetails ( ord_no INT, purch_amt DECIMAL(10, 2), ord_date DATE, customer_id INT ); INSERT INTO orderdetails VALUES (70009, 2700, '2012-09-10', 3001); INSERT INTO orderdetails VALUES (70008, 1760, '2012-09-10', 3002); INSERT INTO orderdetails VALUES (70002, 500, '2012-10-05', 3002); INSERT INTO orderdetails VALUES (70002, 250, '2012-10-05', 3003); INSERT INTO orderdetails VALUES (70013, 3045, '2012-09-25', 3004); INSERT INTO orderdetails VALUES (70013, 685, '2012-09-25', 3005); INSERT INTO orderdetails VALUES (70011, 752, '2012-10-17', 3005); INSERT INTO orderdetails VALUES (70010, 1983, '2012-10-10', 3006); select * from orderdetails; with cte as( select customer_id,sum(purch_amt) amt , month (ord_date) month from orderdetails group by customer_id,month(ord_date)) select * from (select cte.* ,c.city,c.gender, rank () over (partition by cte.month,c.city order by cte.amt desc ) rn from cte inner join customer c on cte.customer_id =c.customer_id) k where rn=1;