SQLize Online / PHPize Online  /  SQLtest Online

------Ankiit---- CREATE TABLE users ( USER_ID INT PRIMARY KEY, USER_NAME VARCHAR(20) NOT NULL, USER_STATUS VARCHAR(20) NOT NULL ); CREATE TABLE logins ( USER_ID INT, LOGIN_TIMESTAMP DATETIME NOT NULL, SESSION_ID INT PRIMARY KEY, SESSION_SCORE INT, FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID) ); -- Users Table INSERT INTO USERS VALUES (1, 'Alice', 'Active'); INSERT INTO USERS VALUES (2, 'Bob', 'Inactive'); INSERT INTO USERS VALUES (3, 'Charlie', 'Active'); INSERT INTO USERS VALUES (4, 'David', 'Active'); INSERT INTO USERS VALUES (5, 'Eve', 'Inactive'); INSERT INTO USERS VALUES (6, 'Frank', 'Active'); INSERT INTO USERS VALUES (7, 'Grace', 'Inactive'); INSERT INTO USERS VALUES (8, 'Heidi', 'Active'); INSERT INTO USERS VALUES (9, 'Ivan', 'Inactive'); INSERT INTO USERS VALUES (10, 'Judy', 'Active'); --user logins INSERT INTO LOGINS VALUES (1, '2023-07-15 09:30:00​', 1001, 85); INSERT INTO LOGINS VALUES (2, '2023-07-22 10:00:00​', 1002, 90); INSERT INTO LOGINS VALUES (3, '2023-08-10 11:15:00​', 1003, 75); INSERT INTO LOGINS VALUES (4, '2023-08-20 14:00:00​', 1004, 88); INSERT INTO LOGINS VALUES (5, '2023-09-05 16:45:00​', 1005, 82); INSERT INTO LOGINS VALUES (6, '2023-10-12 08:30:00​', 1006, 77); INSERT INTO LOGINS VALUES (7, '2023-11-18 09:00:00​', 1007, 81); INSERT INTO LOGINS VALUES (8, '2023-12-01 10:30:00​', 1008, 84); INSERT INTO LOGINS VALUES (9, '2023-12-15 13:15:00​', 1009, 79); -- 2024 Q1 INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (1, '2024-01-10 07:45:00​', 1011, 86); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (2, '2024-01-25 09:30:00​', 1012, 89); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (3, '2024-02-05 11:00:00​', 1013, 78); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (4, '2024-03-01 14:30:00​', 1014, 91); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (5, '2024-03-15 16:00:00​', 1015, 83); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (6, '2024-04-12 08:00:00​', 1016, 80); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (7, '2024-05-18 09:15:00​', 1017, 82); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (8, '2024-05-28 10:45:00​', 1018, 87); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (9, '2024-06-15 13:30:00​', 1019, 76); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-25 15:00:00​', 1010, 92); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-26 15:45:00​', 1020, 93); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-27 15:00:00​', 1021, 92); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-28 15:45:00​', 1022, 93); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (1, '2024-01-10 07:45:00​', 1101, 86); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (3, '2024-01-25 09:30:00​', 1102, 89); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (5, '2024-01-15 11:00:00​', 1103, 78); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (2, '2023-11-10 07:45:00​', 1201, 82); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (4, '2023-11-25 09:30:00​', 1202, 84); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (6, '2023-11-15 11:00:00​', 1203, 80); --q1-->mangament want to see which usee not logins past 5 years select count(*) from logins; /* create table emails( email varchar(25)) ; insert into emails values( 'atif@gmail.com'), ( 'khalid@outuk.com'), ('ash@yahooo.com'); select substring(email,charindex('@',email)+1, len(email)) from emails; --rough CREATE TABLE orders ( order_id INT NOT NULL, user_id INT NOT NULL, r_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, order_date DATE NOT NULL, partner_id INT NOT NULL, delivery_time INT NOT NULL, delivery_rating INT NULL, restaurant_rating INT NULL ); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1001, 1, 1, 550.00, '2022-05-10', 1, 25, 5, 3); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1002, 1, 2, 415.00, '2022-05-26', 1, 19, 5, 2); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1003, 1, 3, 240.00, '2022-06-15', 5, 29, 4, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1004, 1, 3, 240.00, '2022-06-29', 4, 42, 3, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1005, 1, 3, 220.00, '2022-07-10', 1, 58, 1, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1006, 2, 1, 950.00, '2022-06-10', 2, 16, 5, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1007, 2, 2, 530.00, '2022-06-23', 3, 60, 1, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1008, 2, 3, 240.00, '2022-07-07', 5, 33, 4, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1009, 2, 4, 300.00, '2022-07-17', 4, 41, 1, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1010, 2, 5, 650.00, '2022-07-31', 1, 67, 1, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1011, 3, 1, 450.00, '2022-05-10', 2, 25, 3, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1012, 3, 4, 180.00, '2022-05-20', 5, 33, 4, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1013, 3, 2, 230.00, '2022-05-30', 4, 45, 3, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating ) VALUES (1014, 3, 2, 230.00, '2022-06-11', 2, 55, 1, 2); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1015, 3, 2, 230.00, '2022-06-22', 3, 21, 5, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1016, 4, 4, 300.00, '2022-05-15', 3, 31, 5, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1017, 4, 4, 300.00, '2022-05-30', 1, 50, 1, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1018, 4, 4, 400.00, '2022-06-15', 2, 40, 3, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1019, 4, 5, 400.00, '2022-06-30', 1, 70, 2, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1020, 4, 5, 400.00, '2022-07-15', 3, 26, 5, 3); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1021, 5, 1, 550.00, '2022-07-01', 5, 22, 2, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1022, 5, 1, 550.00, '2022-07-08', 1, 34, 5, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1023, 5, 2, 645.00, '2022-07-15', 4, 38, 5, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1024, 5, 2, 645.00, '2022-07-21', 2, 58, 2, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1025, 5, 2, 645.00, '2022-07-28', 2, 44, 4, NULL); CREATE TABLE partner ( partner_id INT NOT NULL, partner_name VARCHAR(50) NOT NULL ); INSERT INTO partner (partner_id, partner_name) VALUES (1, 'Suresh'); INSERT INTO partner (partner_id, partner_name) VALUES (2, 'Amit'); INSERT INTO partner (partner_id, partner_name) VALUES (3, 'Lokesh'); INSERT INTO partner (partner_id, partner_name) VALUES (4, 'Kartik'); INSERT INTO partner (partner_id, partner_name) VALUES (5, 'Gyandeep'); create table order_data( id int, order_id int, f_id int ); INSERT INTO order_data (id, order_id, f_id) VALUES (1, 1001, 1), (2, 1001, 3), (3, 1002, 4), (4, 1002, 3), (5, 1003, 6), (6, 1003, 3), (7, 1004, 6), (8, 1004, 3), (9, 1005, 7), (10, 1005, 3), (11, 1006, 1), (12, 1006, 2), (13, 1006, 3), (14, 1007, 4), (15, 1007, 3), (16, 1008, 6), (17, 1008, 3), (18, 1009, 8), (19, 1009, 9), (20, 1010, 10), (21, 1010, 11), (22, 1010, 6), (23, 1011, 1), (24, 1012, 8), (25, 1013, 4), (26, 1014, 4), (27, 1015, 4), (28, 1016, 8), (29, 1016, 9), (30, 1017, 8), (31, 1017, 9), (32, 1018, 10), (33, 1018, 11), (34, 1019, 10), (35, 1019, 11), (36, 1020, 10), (37, 1020, 11), (38, 1021, 1), (39, 1021, 3), (40, 1022, 1), (41, 1022, 3), (42, 1023, 3), (43, 1023, 4), (44, 1023, 5), (45, 1024, 3), (46, 1024, 4), (47, 1024, 5), (48, 1025, 3), (49, 1025, 4), (50, 1025, 5); CREATE TABLE food ( f_id INT NOT NULL, f_name VARCHAR(100) NOT NULL, type VARCHAR(50) NOT NULL ); INSERT INTO food (f_id, f_name, type) VALUES (1, 'Non-veg Pizza', 'Non-veg'), (2, 'Veg Pizza', 'Veg'), (3, 'Choco Lava cake', 'Veg'), (4, 'Chicken Wings', 'Non-veg'), (5, 'Chicken Popcorn', 'Non-veg'), (6, 'Rice Meal', 'Veg'), (7, 'Roti meal', 'Veg'), (8, 'Masala Dosa', 'Veg'), (9, 'Rava Idli', 'Veg'), (10, 'Schezwan Noodles', 'Veg'), (11, 'Veg Manchurian', 'Veg'); CREATE TABLE menu ( menu_id INT NOT NULL, r_id INT NOT NULL, f_id INT NOT NULL, price DECIMAL(10, 2) NOT NULL ); INSERT INTO menu (menu_id, r_id, f_id, price) VALUES (1, 1, 1, 450), (2, 1, 2, 400), (3, 1, 3, 100), (4, 2, 3, 115), (5, 2, 4, 230), (6, 2, 5, 300), (7, 3, 3, 80), (8, 3, 6, 160), (9, 3, 7, 140), (10, 4, 6, 230), (11, 4, 8, 180), (12, 4, 9, 120), (13, 5, 6, 250), (14, 5, 10, 220), (15, 5, 11, 180); CREATE TABLE restaurant ( r_id INT NOT NULL, r_name VARCHAR(100) NOT NULL, cuisine VARCHAR(50) NOT NULL ); INSERT INTO restaurant (r_id, r_name, cuisine) VALUES (1, 'dominos', 'Italian'), (2, 'kfc', 'American'), (3, 'box8', 'North Indian'), (4, 'Dosa Plaza', 'South Indian'), (5, 'China Town', 'Chinese'); CREATE TABLE [user] ( user_id INT NOT NULL, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, password VARCHAR(50) NOT NULL ); INSERT INTO [user] (user_id, name, email, password) VALUES (1, 'Nitish', 'nitish@gmail.com', 'p252h'), (2, 'Khushboo', 'khushboo@gmail.com', 'hxn9b'), (3, 'Vartika', 'vartika@gmail.com', '9hu7j'), (4, 'Ankit', 'ankit@gmail.com', 'lkko3'), (5, 'Neha', 'neha@gmail.com', '3i7qm'), (6, 'Anupama', 'anupama@gmail.com', '46rdw2'), (7, 'Rishabh', 'rishabh@gmail.com', '4sw123'); --order_data, partner, order, food, menu, restaurant, user select top 2 * from [user]; select top 2 * from restaurant; select top 2 * from menu; select top 2 * from food; select top 2 * from [orders]; select top 2 * from partner; select top 2 * from order_data; --select the customer who have never order select name from [user] where user_id not in(select user_id from orders) --find the average price/dish select f.f_name ,cast( avg(m.price) as decimal (10,2)) price_per_dish from menu m inner join food f on f.f_id=m.f_id group by f.f_name order by price_per_dish; --find the top most busy restaurant each month with cte as( select format(o.order_date, 'yyyy-MM') date, max(r.r_name) rname , count(o.order_id) total_order , row_number() over( partition by format(o.order_date, 'yyyy-MM') order by count (o.order_id) desc) rn from restaurant r inner join orders o on o.r_id=r.r_id group by format(o.order_date, 'yyyy-MM') ,o.r_id ) select * from cte where rn=1; --fill null value with average rating in order_data table select * into new_order from orders; alter table new_order alter column restaurant_rating float; update t1 set t1.restaurant_rating=t2.nr from new_order t1 inner join ( select r_id, cast(isnull(1.00*sum(restaurant_rating)/count(restaurant_rating) ,0) as decimal (10,2)) nr from new_order group by r_id) t2 on t1.r_id=t2.r_id where t1.restaurant_rating is null ; select t1.*, t2.restaurant_rating rr from new_order t1 inner join orders t2 on t1.order_id=t2.order_id; --restaurant monthly sale is greater than xammount select format (o.order_date, 'yyyy-MM') date, r.r_name, sum(o.amount) total_sale from orders o inner join restaurant r on r.r_id=o.r_id group by format (o.order_date, 'yyyy-MM'),r.r_name having sum(amount)>1000 order by date,total_sale desc; --show all customer_details during a period for a particular person(let ankit) with cte as( select o.order_id,f.type, string_agg(f.f_name,',') food_ate from new_order o inner join order_data d on o.order_id=d.order_id inner join food f on f.f_id=d.f_id group by o.order_id, f.type) select u.name,p.partner_name,r.r_name,c.type ,c.food_ate, o.amount, o.delivery_rating,o.restaurant_rating from orders o inner join [user] u on u.user_id=o.user_id inner join restaurant r --to get restaurant_name on r.r_id=o.r_id inner join cte c --food ate on o.order_id=c.order_id inner join partner p --for partner on o.partner_id=p.partner_id where u.name='Ankit' and o.order_date between '2022-06-10' and '2022-07-10' order by o.order_date ,o.amount desc; --find the restaurant max no of repatativee customer name with cte as(select r_id, user_id, count(user_id) repeat, sum(restaurant_rating)/count (restaurant_rating) rate ,dense_rank() over(partition by r_id order by count(user_id) desc) rn from new_order group by r_id , user_id having count(user_id)>1 ) select r.r_name, u.name , cte.repeat, cte.rate from cte inner join [user] u on u.user_id=cte.user_id inner join restaurant r on cte.r_id=r.r_id order by cte.r_id, cte.rn; --find the no of loyal customer in restaurant select top 1 r.r_name,count(o.cnt) loyal_customer from restaurant r inner join (select r_id,user_id, count(user_id) cnt from orders group by r_id, user_id having count (user_id)>1) o on o.r_id=r.r_id group by r.r_name order by loyal_customer desc ; --mom growth select date,iif(lag=0,total_amount,100.00*(total_amount-lag) /lag) MOM from (select format (order_date, 'yyyy-MM') date, sum(amount) total_amount,lag(sum(amount) , 1) over(order by format (order_date, 'yyyy-MM') ) lag from orders group by format (order_date, 'yyyy-MM') ) k; --custmer fav food with cte as( select u.name,f.f_name, count(f_name) no_oforders, dense_rank() over(partition by u.name order by count (f.f_name) desc) rn from order_data od inner join orders o on o.order_id=od.order_id inner join food f on f.f_id=od.f_id inner join [user] u on o.user_id=u.user_id group by u.name, f.f_name) select name, f_name from cte where rn=1; --most paired food with cte as( select f1.f_name food1,f2.f_name food2 , count(*) paired_order, dense_rank() over(order by count(*) desc) rn from order_data o1 inner join order_data o2 on (o1.order_id=o2.order_id) and (o1.f_id<>o2.f_id) and(o1.f_id>o2.f_id) inner join food f1 on (o1.f_id =f1.f_id) inner join food f2 on o2.f_id=f2.f_id group by f1.f_name,f2.f_name) select * from cte where rn=1; with cte as( select o.order_id, f.f_name from order_data o inner join food f on o.f_id=f.f_id ), cte2 as( select t1.f_name food1, t2.f_name food2, count(*) paired_order, dense_rank() over(order by count (*) desc) rn from cte t1 inner join cte t2 on (t1.order_id=t2.order_id) and(t1.f_name<>t2.f_name) and (t1.f_name>t2.f_name) group by t1.f_name, t2.f_name ) select * from cte2 where rn=1; select * from order_data; --select * from orders; --select * from food; --rough CREATE TABLE orders ( order_id INT NOT NULL, user_id INT NOT NULL, r_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, order_date DATE NOT NULL, partner_id INT NOT NULL, delivery_time INT NOT NULL, delivery_rating INT NULL, restaurant_rating INT NULL ); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1001, 1, 1, 550.00, '2022-05-10', 1, 25, 5, 3); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1002, 1, 2, 415.00, '2022-05-26', 1, 19, 5, 2); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1003, 1, 3, 240.00, '2022-06-15', 5, 29, 4, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1004, 1, 3, 240.00, '2022-06-29', 4, 42, 3, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1005, 1, 3, 220.00, '2022-07-10', 1, 58, 1, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1006, 2, 1, 950.00, '2022-06-10', 2, 16, 5, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1007, 2, 2, 530.00, '2022-06-23', 3, 60, 1, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1008, 2, 3, 240.00, '2022-07-07', 5, 33, 4, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1009, 2, 4, 300.00, '2022-07-17', 4, 41, 1, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1010, 2, 5, 650.00, '2022-07-31', 1, 67, 1, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1011, 3, 1, 450.00, '2022-05-10', 2, 25, 3, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1012, 3, 4, 180.00, '2022-05-20', 5, 33, 4, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1013, 3, 2, 230.00, '2022-05-30', 4, 45, 3, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating ) VALUES (1014, 3, 2, 230.00, '2022-06-11', 2, 55, 1, 2); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1015, 3, 2, 230.00, '2022-06-22', 3, 21, 5, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1016, 4, 4, 300.00, '2022-05-15', 3, 31, 5, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1017, 4, 4, 300.00, '2022-05-30', 1, 50, 1, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1018, 4, 4, 400.00, '2022-06-15', 2, 40, 3, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1019, 4, 5, 400.00, '2022-06-30', 1, 70, 2, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1020, 4, 5, 400.00, '2022-07-15', 3, 26, 5, 3); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1021, 5, 1, 550.00, '2022-07-01', 5, 22, 2, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1022, 5, 1, 550.00, '2022-07-08', 1, 34, 5, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1023, 5, 2, 645.00, '2022-07-15', 4, 38, 5, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1024, 5, 2, 645.00, '2022-07-21', 2, 58, 2, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1025, 5, 2, 645.00, '2022-07-28', 2, 44, 4, NULL); CREATE TABLE partner ( partner_id INT NOT NULL, partner_name VARCHAR(50) NOT NULL ); INSERT INTO partner (partner_id, partner_name) VALUES (1, 'Suresh'); INSERT INTO partner (partner_id, partner_name) VALUES (2, 'Amit'); INSERT INTO partner (partner_id, partner_name) VALUES (3, 'Lokesh'); INSERT INTO partner (partner_id, partner_name) VALUES (4, 'Kartik'); INSERT INTO partner (partner_id, partner_name) VALUES (5, 'Gyandeep'); create table order_data( id int, order_id int, f_id int ); INSERT INTO order_data (id, order_id, f_id) VALUES (1, 1001, 1), (2, 1001, 3), (3, 1002, 4), (4, 1002, 3), (5, 1003, 6), (6, 1003, 3), (7, 1004, 6), (8, 1004, 3), (9, 1005, 7), (10, 1005, 3), (11, 1006, 1), (12, 1006, 2), (13, 1006, 3), (14, 1007, 4), (15, 1007, 3), (16, 1008, 6), (17, 1008, 3), (18, 1009, 8), (19, 1009, 9), (20, 1010, 10), (21, 1010, 11), (22, 1010, 6), (23, 1011, 1), (24, 1012, 8), (25, 1013, 4), (26, 1014, 4), (27, 1015, 4), (28, 1016, 8), (29, 1016, 9), (30, 1017, 8), (31, 1017, 9), (32, 1018, 10), (33, 1018, 11), (34, 1019, 10), (35, 1019, 11), (36, 1020, 10), (37, 1020, 11), (38, 1021, 1), (39, 1021, 3), (40, 1022, 1), (41, 1022, 3), (42, 1023, 3), (43, 1023, 4), (44, 1023, 5), (45, 1024, 3), (46, 1024, 4), (47, 1024, 5), (48, 1025, 3), (49, 1025, 4), (50, 1025, 5); CREATE TABLE food ( f_id INT NOT NULL, f_name VARCHAR(100) NOT NULL, type VARCHAR(50) NOT NULL ); INSERT INTO food (f_id, f_name, type) VALUES (1, 'Non-veg Pizza', 'Non-veg'), (2, 'Veg Pizza', 'Veg'), (3, 'Choco Lava cake', 'Veg'), (4, 'Chicken Wings', 'Non-veg'), (5, 'Chicken Popcorn', 'Non-veg'), (6, 'Rice Meal', 'Veg'), (7, 'Roti meal', 'Veg'), (8, 'Masala Dosa', 'Veg'), (9, 'Rava Idli', 'Veg'), (10, 'Schezwan Noodles', 'Veg'), (11, 'Veg Manchurian', 'Veg'); CREATE TABLE menu ( menu_id INT NOT NULL, r_id INT NOT NULL, f_id INT NOT NULL, price DECIMAL(10, 2) NOT NULL ); INSERT INTO menu (menu_id, r_id, f_id, price) VALUES (1, 1, 1, 450), (2, 1, 2, 400), (3, 1, 3, 100), (4, 2, 3, 115), (5, 2, 4, 230), (6, 2, 5, 300), (7, 3, 3, 80), (8, 3, 6, 160), (9, 3, 7, 140), (10, 4, 6, 230), (11, 4, 8, 180), (12, 4, 9, 120), (13, 5, 6, 250), (14, 5, 10, 220), (15, 5, 11, 180); CREATE TABLE restaurant ( r_id INT NOT NULL, r_name VARCHAR(100) NOT NULL, cuisine VARCHAR(50) NOT NULL ); INSERT INTO restaurant (r_id, r_name, cuisine) VALUES (1, 'dominos', 'Italian'), (2, 'kfc', 'American'), (3, 'box8', 'North Indian'), (4, 'Dosa Plaza', 'South Indian'), (5, 'China Town', 'Chinese'); CREATE TABLE [user] ( user_id INT NOT NULL, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, password VARCHAR(50) NOT NULL ); INSERT INTO [user] (user_id, name, email, password) VALUES (1, 'Nitish', 'nitish@gmail.com', 'p252h'), (2, 'Khushboo', 'khushboo@gmail.com', 'hxn9b'), (3, 'Vartika', 'vartika@gmail.com', '9hu7j'), (4, 'Ankit', 'ankit@gmail.com', 'lkko3'), (5, 'Neha', 'neha@gmail.com', '3i7qm'), (6, 'Anupama', 'anupama@gmail.com', '46rdw2'), (7, 'Rishabh', 'rishabh@gmail.com', '4sw123'); --order_data, partner, order, food, menu, restaurant, user select top 2 * from [user]; select top 2 * from restaurant; select top 2 * from menu; select top 2 * from food; select top 2 * from [orders]; select top 2 * from partner; select top 2 * from order_data; --select the customer who have never order select name from [user] where user_id not in(select user_id from orders) --find the average price/dish select f.f_name ,cast( avg(m.price) as decimal (10,2)) price_per_dish from menu m inner join food f on f.f_id=m.f_id group by f.f_name order by price_per_dish; --find the top most busy restaurant each month with cte as( select format(o.order_date, 'yyyy-MM') date, max(r.r_name) rname , count(o.order_id) total_order , row_number() over( partition by format(o.order_date, 'yyyy-MM') order by count (o.order_id) desc) rn from restaurant r inner join orders o on o.r_id=r.r_id group by format(o.order_date, 'yyyy-MM') ,o.r_id ) select * from cte where rn=1; --fill null value with average rating in order_data table select * into new_order from orders; alter table new_order alter column restaurant_rating float; update t1 set t1.restaurant_rating=t2.nr from new_order t1 inner join ( select r_id, cast(isnull(1.00*sum(restaurant_rating)/count(restaurant_rating) ,0) as decimal (10,2)) nr from new_order group by r_id) t2 on t1.r_id=t2.r_id where t1.restaurant_rating is null ; select t1.*, t2.restaurant_rating rr from new_order t1 inner join orders t2 on t1.order_id=t2.order_id; --restaurant monthly sale is greater than xammount select format (o.order_date, 'yyyy-MM') date, r.r_name, sum(o.amount) total_sale from orders o inner join restaurant r on r.r_id=o.r_id group by format (o.order_date, 'yyyy-MM'),r.r_name having sum(amount)>1000 order by date,total_sale desc; --show all customer_details during a period for a particular person(let ankit) with cte as( select o.order_id,f.type, string_agg(f.f_name,',') food_ate from new_order o inner join order_data d on o.order_id=d.order_id inner join food f on f.f_id=d.f_id group by o.order_id, f.type) select u.name,p.partner_name,r.r_name,c.type ,c.food_ate, o.amount, o.delivery_rating,o.restaurant_rating from orders o inner join [user] u on u.user_id=o.user_id inner join restaurant r --to get restaurant_name on r.r_id=o.r_id inner join cte c --food ate on o.order_id=c.order_id inner join partner p --for partner on o.partner_id=p.partner_id where u.name='Ankit' and o.order_date between '2022-06-10' and '2022-07-10' order by o.order_date ,o.amount desc; --find the restaurant max no of repatativee customer name with cte as(select r_id, user_id, count(user_id) repeat, sum(restaurant_rating)/count (restaurant_rating) rate ,dense_rank() over(partition by r_id order by count(user_id) desc) rn from new_order group by r_id , user_id having count(user_id)>1 ) select r.r_name, u.name , cte.repeat, cte.rate from cte inner join [user] u on u.user_id=cte.user_id inner join restaurant r on cte.r_id=r.r_id order by cte.r_id, cte.rn; --find the no of loyal customer in restaurant select top 1 r.r_name,count(o.cnt) loyal_customer from restaurant r inner join (select r_id,user_id, count(user_id) cnt from orders group by r_id, user_id having count (user_id)>1) o on o.r_id=r.r_id group by r.r_name order by loyal_customer desc ; --mom growth select date,iif(lag=0,total_amount,100.00*(total_amount-lag) /lag) MOM from (select format (order_date, 'yyyy-MM') date, sum(amount) total_amount,lag(sum(amount) , 1) over(order by format (order_date, 'yyyy-MM') ) lag from orders group by format (order_date, 'yyyy-MM') ) k; --custmer fav food with cte as( select u.name,f.f_name, count(f_name) no_oforders, dense_rank() over(partition by u.name order by count (f.f_name) desc) rn from order_data od inner join orders o on o.order_id=od.order_id inner join food f on f.f_id=od.f_id inner join [user] u on o.user_id=u.user_id group by u.name, f.f_name) select name, f_name from cte where rn=1; --most paired food with cte as( select f1.f_name food1,f2.f_name food2 , count(*) paired_order, dense_rank() over(order by count(*) desc) rn from order_data o1 inner join order_data o2 on (o1.order_id=o2.order_id) and (o1.f_id<>o2.f_id) and(o1.f_id>o2.f_id) inner join food f1 on (o1.f_id =f1.f_id) inner join food f2 on o2.f_id=f2.f_id group by f1.f_name,f2.f_name) select * from cte where rn=1; with cte as( select o.order_id, f.f_name from order_data o inner join food f on o.f_id=f.f_id ), cte2 as( select t1.f_name food1, t2.f_name food2, count(*) paired_order, dense_rank() over(order by count (*) desc) rn from cte t1 inner join cte t2 on (t1.order_id=t2.order_id) and(t1.f_name<>t2.f_name) and (t1.f_name>t2.f_name) group by t1.f_name, t2.f_name ) select * from cte2 where rn=1; select * from order_data; --select * from orders; --select * from food; --featch submission_day, occurance, hacker_id in each day --occurance (unique hacker who participated each day) --hacker_id (most time submitted in a day weather it's is submitted all day Or not if there is tie in occurance the featch min hacker_id)) CREATE TABLE Submissions ( submission_date DATE, submission_id INT PRIMARY KEY, hacker_id INT, score INT ); INSERT INTO Submissions (submission_date, submission_id, hacker_id, score) VALUES ('2016-03-01', 8494, 20703, 0), ('2016-03-01', 22403, 53473, 15), ('2016-03-01', 23965, 79722, 60), ('2016-03-01', 30173, 36396, 70), ('2016-03-02', 34928, 20703, 0), ('2016-03-02', 38740, 15758, 60), ('2016-03-02', 42769, 79722, 25), ('2016-03-02', 44364, 79722, 60), ('2016-03-03', 45440, 20703, 0), ('2016-03-03', 49050, 36396, 70), ('2016-03-03', 50273, 79722, 5), ('2016-03-04', 50344, 20703, 0), ('2016-03-04', 51360, 44065, 90), ('2016-03-04', 54404, 53473, 65), ('2016-03-04', 61533, 79722, 15), ('2016-03-05', 72852, 20703, 0), ('2016-03-05', 74546, 38289, 0), ('2016-03-05', 76487, 62529, 0), ('2016-03-05', 82439, 36396, 10), ('2016-03-05', 90006, 36396, 40), ('2016-03-06', 90404, 20703, 0); with cte as( select *, case when dense_rank() over(order by submission_date) -dense_rank() over(partition by hacker_id order by submission_date) =0 then hacker_id else null end as cumulative_hacker, count(hacker_id) over(partition by hacker_id, submission_date order by submission_date) hacker_count from submissions ), cte2 as ( select *, row_number() over(partition by submission_date order by hacker_count desc, hacker_id asc) hacker_flag -- count(cumulative_hacker) over(partition by submission_date) unique_hacker from cte ) select cte2.submission_date, cte2.hacker_id, k.unique_count from cte2 inner join ( select submission_date,count(distinct cumulative_hacker) unique_count from cte2 group by submission_date) k on k.submission_date=cte2.submission_date where cte2.hacker_flag=1 order by cte2.submission_date; /* with cte as( select * ,count(hacker_id) over(partition by submission_date,hacker_id ) hacker_count ,case when day(submission_date)-dense_rank() over(partition by hacker_id order by submission_date asc)= 0 then hacker_id else null end Countnous_hackerid from submissions ) , cte2 as (select *, row_number() over(partition by submission_date order by hacker_count desc, hacker_id asc) hackercount_flag from cte ) select cte2.submission_date, k.count, cte2.hacker_id from cte2 inner join ( select submission_date, count(distinct Countnous_hackerid) count from cte group by submission_date ) k on cte2.submission_date=k.submission_date where cte2.hackercount_flag=1;*/ --fetch the first date of each work when it was assigned to the last person CREATE TABLE [dbo].[AssignedTo] ( [WorkId] [INT] NOT NULL, [OwnerId] [INT] NOT NULL, [ValidFrom] [DATETIME] NULL ) ON [PRIMARY]; INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-01'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 2, '2020-01-02'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-03'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-04'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 2, '2020-01-05'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 2, '2020-01-06'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 2, '2020-01-07'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-08'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-09'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (1, 1, '2020-01-10'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (2, 3, '2020-01-05'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (2, 4, '2020-01-06'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (2, 4, '2020-01-07'); INSERT INTO dbo.AssignedTo (WorkId, OwnerId, ValidFrom) VALUES (3, 5, '2020-01-06'); with cte as( select t2.*, row_number() over( partition by t2.workid order by day(t1.validfrom)+day(t2.validfrom) desc) rn from dbo.AssignedTo t1 inner join dbo.AssignedTo t2 on t1.workid=t2.workid and t1.ownerid<>t2.ownerid and datediff (day, t1.validfrom, t2.validfrom)=1) select workid, ownerid , validfrom from cte where rn=1 union all select workid, max(ownerid) , max(validfrom ) from dbo.AssignedTo group by workid having count ( distinct ownerid)=1; create table celeb( name varchar(30)); insert into celeb values ('virat kohli'), ('Narendra damodar modi') , ('salman'); with cte as( select *,len(name)-len(replace(name,' ',''))no_of_space from celeb ) select * from cte; /* --fetch 3rd higest salary -- fetch lowest sal if department count(*) <3 CREATE TABLE Employee ( emp_id int NULL, emp_name varchar(50) NULL, salary int NULL, manager_id int NULL, emp_age int NULL, dep_id int NULL, dep_name varchar(20) NULL, gender varchar(10) NULL ); Insert into Employee values (1,'Ankit', 14300,4,39,100,'Analytics','Female'), (2,'Mohit', 14000,5,48,200,'IT','Male'), (3,'Vikas', 12100,4,37,100,'Analytics','Female'), (4,'Rohit', 7260,2,16,100,'Analytics','Female'), (5,'Mudit', 15000,6,55,200,'IT','Male'), (6,'Agam', 15600,2,14,200,'IT','Male'), (7,'Sanjay', 12000,2,13,200,'IT','Male'), (8,'Ashish',7200,2,12,200,'IT','Male'), (9,'Mukesh', 7000,6,51,300,'HR','Male'), (10,'Rakesh',8000,6,50,300,'HR','Male'), (11,'Akhil',4000,1,31,500,'Ops','Male'); with cte as( select *,row_number() over( partition by dep_name order by salary desc ) rn, count(*) over( partition by dep_name order by (select null) rows between unbounded preceding and unbounded following) cnt from employee ) --select * from cte order by dep_name, rn;/* select * from cte where ( case when cnt>=3 and rn=3 then 1 when cnt=2 and rn=2 then 1 when cnt=1 and rn=1 then 1 end) =1; with cte as( select *,row_number() over( partition by dep_name order by salary) rn, count(*) over( partition by dep_name) cnt from employee ) select * from cte where rn=3 or(cnt<3 and rn=cnt ); --fetch the record when 3 consecutive stock_quantity less than 50 --output_tabl>supplier_id,product_id, first_date, no_of&record create table stock( supplier_id int, product_id int , stock_quantity int , record_date date ) INSERT INTO stock (supplier_id, product_id, stock_quantity, record_date) VALUES (1, 1, 60, '2022-01-01'), (1, 1, 40, '2022-01-02'), (1, 1, 35, '2022-01-03'), (1, 1, 45, '2022-01-04'), (1, 1, 51, '2022-01-06'), (1, 1, 55, '2022-01-09'), (1, 1, 25, '2022-01-10'), (1, 1, 48, '2022-01-11'), (1, 1, 45, '2022-01-15'), (1, 1, 38, '2022-01-16'), (1, 2, 45, '2022-01-08'), (1, 2, 40, '2022-01-09'), (2, 1, 45, '2022-01-06'), (2, 1, 55, '2022-01-07'), (2, 2, 45, '2022-01-08'), (2, 2, 48, '2022-01-09'), (2, 2, 35, '2022-01-10'), (2, 2, 52, '2022-01-15'), (2, 2, 23, '2022-01-16'); --best with cte as(select *, dateadd(day, -1*row_number() over(partition by supplier_id, product_id order by record_date),record_date) grp from stock where stock_quantity<50) select supplier_id,product_id, min(record_date) first_date, count(*) as no_of_record from cte group by supplier_id, product_id, grp having count(*) >1; --first attempt with cte as(select *, iif(stock_quantity<50, 0,stock_quantity) base_quantity from stock) , cte2 as(select *, row_number() over(partition by supplier_id, product_id order by base_quantity) - day(record_date) as k from cte ) select supplier_id, product_id,min(record_date) d,count(*) from cte2 where base_quantity=0 group by supplier_id, product_id,k having count(*)>1; --give start to a movie create table movies(id int primary key, genre varchar(20), title varchar(50) ); --Create reviews table CREATE TABLE reviews ( movie_id INT, rating DECIMAL(3,1), FOREIGN KEY (movie_id) REFERENCES movies(id) ); --Insert sample data into movies table INSERT INTO movies (id, genre, title) VALUES (1, 'Action', 'The Dark Knight'), (2, 'Action', 'Avengers: Infinity War'), (3, 'Action', 'Gladiator'), (4, 'Action', 'Die Hard'), (5, 'Action', 'Mad Max: Fury Road'), (6, 'Drama', 'The Shawshank Redemption'), (7, 'Drama', 'Forrest Gump'), (8, 'Drama', 'The Godfather'), (9, 'Drama', 'Schindler"s List'), (10, 'Drama', 'Fight Club'), (11, 'Comedy', 'The Hangover'), (12, 'Comedy', 'Superbad'), (13, 'Comedy', 'Dumb and Dumber'), (14, 'Comedy', 'Bridesmaids'), (15, 'Comedy', 'Anchorman: The Legend of Ron Burgundy'); --Insert sample data into reviews table INSERT INTO reviews (movie_id, rating) VALUES (1, 4.5), (1, 4.0), (1, 5.0), (2, 4.2), (2, 4.8), (2, 3.9), (3, 4.6), (3, 3.8), (3, 4.3), (4, 4.1), (4, 3.7), (4, 4.4), (5, 3.9), (5, 4.5), (5, 4.2), (6, 4.8), (6, 4.7), (6, 4.9), (7, 4.6), (7, 4.9), (7, 4.3), (8, 4.9), (8, 5.0), (8, 4.8), (9, 4.7), (9, 4.9),(9, 4.5), (10, 4.6), (10, 4.3), (10, 4.7), (11, 3.9), (11, 4.0), (11, 3.5), (12, 3.7), (12, 3.8), (12, 4.2), (13, 3.2), (13, 3.5), (13, 3.8), (14, 3.8), (14, 4.0), (14, 4.2), (15, 3.9), (15, 4.0), (15, 4.1),(9, 4.5), (10, 4.6), (10, 4.3), (10, 4.7), (11, 3.9), (11, 4.0), (11, 3.5), (12, 3.7), (12, 3.8), (12, 4.2), (13, 3.2), (13, 3.5), (13, 3.8), (14, 3.8), (14, 4.0), (14, 4.2), (15, 3.9), (15, 4.0), (15, 4.1); select genre,title ,title,rating--iif(rating=5, '*****',iif(rating=4,'****',iif(rating=3,'***'))) ,case when rating=5 then '*****' when rating =4 then '****' when rating=3 then'***' when rating =2 then '**' else '*' end rating from (select m.genre,m.title,round(avg(r.rating), 0) rating, row_number() over(partition by m.genre order by avg(r.rating) desc) rnk from movies m inner join reviews r on m.id=r.movie_id group by m.genre,m.title) k where rnk=1; select genre,title ,title,rating, replicate ('*', rating) from (select m.genre,m.title,round(avg(r.rating), 0) rating, row_number() over(partition by m.genre order by avg(r.rating) desc) rnk from movies m inner join reviews r on m.id=r.movie_id group by m.genre,m.title) k where rnk=1; -->bom-del is different from del-bom route -->fetch route having maximum no of ticket_count CREATE TABLE tickets ( airline_number VARCHAR(10), origin VARCHAR(3), destination VARCHAR(3), oneway_round CHAR(1), ticket_count INT ); INSERT INTO tickets (airline_number, origin, destination, oneway_round, ticket_count) VALUES ('DEF456', 'BOM', 'DEL', 'O', 150), ('GH1789', 'DEL', 'BOM', 'R', 50), ('JKL012', 'BOM', 'DEL', 'R', 75), ('MNO345', 'DEL', 'NYC', 'O', 200), ('PQR678', 'NYC', 'DEL', 'O', 180), ('STU901', 'NYC', 'DEL', 'R', 60), ('ABC123', 'DEL', 'BOM', 'O', 100), ('VWX234', 'DEL', 'NYC', 'R', 90); --select * from tickets; /*select least(origin, destination), greatest (origin, destination) ,sum(ticket_count) from tickets group by least(origin, destination), greatest (origin, destination) order by sum(ticket_count) desc; */ select origin, destination, sum(ticket_count) s from tickets group by origin, destination order by s desc; select origin, destination, sum(iif(oneway_round='r', 2*ticket_count,ticket_count)) s from tickets group by origin, destination order by s desc; select top 1 origin, destination , sum(ticket_count) s from ( select origin, destination, ticket_count from tickets union all select destination, origin ,ticket_count from tickets where oneway_round='r') k group by origin, destination order by s desc; --removee duplicate data keeping first CREATE TABLE city_distance ( distance INT, source VARCHAR(512), destination VARCHAR(512) ); delete from city_distance; INSERT INTO city_distance(distance, source, destination) VALUES ('100', 'New Delhi', 'Panipat'); INSERT INTO city_distance(distance, source, destination) VALUES ('200', 'Ambala', 'New Delhi'); INSERT INTO city_distance(distance, source, destination) VALUES ('150', 'Bangalore', 'Mysore'); INSERT INTO city_distance(distance, source, destination) VALUES ('150', 'Mysore', 'Bangalore'); INSERT INTO city_distance(distance, source, destination) VALUES ('250', 'Mumbai', 'Pune'); INSERT INTO city_distance(distance, source, destination) VALUES ('250', 'Pune', 'Mumbai'); INSERT INTO city_distance(distance, source, destination) VALUES ('2500', 'Chennai', 'Bhopal'); INSERT INTO city_distance(distance, source, destination) VALUES ('2500', 'Bhopal', 'Chennai'); INSERT INTO city_distance(distance, source, destination) VALUES ('60', 'Tirupati', 'Tirumala'); INSERT INTO city_distance(distance, source, destination) VALUES ('80', 'Tirumala', 'Tirupati'); select * from city_distance; --sol 1(without order) with cte as ( select *,row_number () over (partition by greatest (source, destination),least(source,destination),distance order by (select null) ) rn from city_distance) select * from cte where rn=1 order by rn; --sol2(with order) with cte as ( select *, row_number() over(order by(select null)) rn from city_distance ) select a.source, a.destination, a.distance from cte a left join cte b on a.source=b.destination and a.destination =b.source and a.distance=b.distance where b.source is null or a.rn<b.rn; CREATE TABLE Orders ( [orderid] INT, [productid] INT, [timestamp] DATETIME2 ) INSERT INTO Orders VALUES (10300,1005,'2024-03-04 16:55:00'), (10303,1005,'2024-03-05 08:25:00'), (10302,1009,'2024-03-04 13:37:00'), (10304,1006,'2024-03-04 11:10:00'), (10301,1007,'2024-03-04 15:26:00'), (10305,1008,'2024-03-04 17:15:00'), (10306,1007,'2024-03-04 19:05:00'), (10340,1008,'2024-03-04 17:17:00'), (10307,1009,'2024-03-04 17:09:00'), (10341,1010,'2024-03-04 18:17:00'), (10347,1011,'2024-03-04 19:09:00'); --select datepart (hour,timestamp) day_name from orders; with cte as( select * , datename (weekday,timestamp) day_name, case when datepart(hour, timestamp) >= 05 and datepart(hour,timestamp) < 12 then 'morning' when datepart(hour, timestamp) >= 12 and datepart(hour, timestamp) < 17 then 'afternoon' else 'evening' end day_time from orders) select day_time, day_name, count(orderid) tc , row_number() over(partition by day_time order by count(orderid)) from cte group by day_time, day_name; --percentage increase current month vs previois cumulative sum CREATE TABLE covid_cases ( record_date DATE PRIMARY KEY, cases_count INT ); insert into covid_cases values ('2021-01-01',66), ('2021-01-02',41), ('2021-01-03',54), ('2021-01-04',68), ('2021-01-05',16), ('2021-01-06',90), ('2021-01-07',34), ('2021-01-08',84),('2021-01-09',71), ('2021-01-10',14),('2021-01-11',48),('2021-01-12',72), ('2021-01-13',55); --select * from covid_cases ; with cte as( select datename(weekday, record_date) day_name , count(cases_count) tcpm from covid_cases group by datename(weekday, record_date) ), cte2 as (select day_name, (1.00*tcpm-lag(tcpm,1,tcpm) over(order by (select null)))/tcpm incra from cte) select *, sum(tcpm) over (order by (select null ) ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) from cte;