SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
------Ankiit---- --American express interview questions --recommend page which is liked by their friends but not like by them -- Create likes table CREATE TABLE likes ( user_id INT, page_id CHAR(1) ); -- Insert data into likes table INSERT INTO likes VALUES (1, 'A'), (1, 'B'), (1, 'C'), (2, 'A'), (3, 'B'), (3, 'C'), (4, 'B'); script: CREATE TABLE friends ( user_id INT, friend_id INT ); -- Insert data into friends table INSERT INTO friends VALUES (1, 2), (1, 3), (1, 4), (2, 1), (3, 1), (3, 4), (4, 1), (4, 3); with cte as( select f.user_id as f_id, l.page_id as friend_page, l2.page_id as own_page, f.friend_id from friends f inner join likes l on f.friend_id=l.user_id left join likes l2 on l.page_id=l2.page_id and f.user_id=l2.user_id) select distinct f_id, friend_page as recommend_page from cte where own_page is null; --solution table select distinct f.user_id as f_id,l.page_id as recommend_page from friends f inner join likes l on f.friend_id=l.user_id left join likes l2 on l.page_id=l2.page_id and f.user_id=l2.user_id where l2.page_id is null ; --find consecutive seats CREATE TABLE cinema ( seat_id INT PRIMARY KEY, free int ); delete from cinema; INSERT INTO cinema (seat_id, free) VALUES (3, 1); INSERT INTO cinema (seat_id, free) VALUES (2, 0); INSERT INTO cinema (seat_id, free) VALUES (1, 1); INSERT INTO cinema (seat_id, free) VALUES (4, 1); INSERT INTO cinema (seat_id, free) VALUES (5, 1); INSERT INTO cinema (seat_id, free) VALUES (6, 0); INSERT INTO cinema (seat_id, free) VALUES (7, 1); INSERT INTO cinema (seat_id, free) VALUES (8, 1); INSERT INTO cinema (seat_id, free) VALUES (9, 0); INSERT INTO cinema (seat_id, free) VALUES (10, 1); INSERT INTO cinema (seat_id, free) VALUES (11, 0); INSERT INTO cinema (seat_id, free) VALUES (12, 1); INSERT INTO cinema (seat_id, free) VALUES (13, 0); INSERT INTO cinema (seat_id, free) VALUES (14, 1); INSERT INTO cinema (seat_id, free) VALUES (15, 1); INSERT INTO cinema (seat_id, free) VALUES (16, 0); INSERT INTO cinema (seat_id, free) VALUES (17, 1); INSERT INTO cinema (seat_id, free) VALUES (18, 1); INSERT INTO cinema (seat_id, free) VALUES (19, 1); INSERT INTO cinema (seat_id, free) VALUES (20, 1); /*with cte as( select *, row_number() over(order by seat_id) -row_number() over(partition by free order by seat_id) flag from cinema ) select * from (select *, count(seat_id) over(partition by flag ) cnt from cte where free<>0) k where cnt>1;*/ with cte as( select *, row_number() over(order by (select null)) rm, row_number() over(partition by free order by (select null)) flag from cinema ) select * from (select *, count(seat_id) over(partition by flag ) cnt from cte where free<>0) k -- Create tables 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'); -- Insert values into 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 user not logins past 5 month --code1--> with q1 as(select b.user_name, Datediff (month,max(a.login_timestamp),getdate()) flag from logins a inner join users b on a.user_id=b.user_id group by a.user_id, b.user_name) select user_name from q1 where flag>=5; --code2 select b.user_name from logins a inner join users b on a.user_id=b.user_id group by a.user_id, b.user_name having max(a.login_timestamp)<dateadd(month, -5, getdate() ); --q2-->anyalysis data quarterly --col--no of seasons, no of users --return first day, User count, season count select datetrunc(quarter,convert(date,min(login_timestamp))) date, count(distinct user_id) count_user, count(session_id) season_count from logins group by datename(quarter, login_timestamp) order by date; --que3-->find the user which is login jan 2024 and did not login November 2023 --INSERT INTO logins (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (1, '2023-09-18 11:00:00', 198, 80); select distinct user_id from logins where Datetrunc (month, convert(date,login_timestamp))='2024-01-01' and user_id not in ( select user_id from logins where Datetrunc (month, convert(date,login_timestamp))='2023-11-01' ); --que4-->find the percentage change session of previous quarter --col-->first day of quarter, season count, season cnt previous, season percent with q4 as ( select datetrunc(quarter,convert(date,min(login_timestamp))) date, count(distinct user_id) count_user, count(session_id) session_count from logins group by datename(quarter, login_timestamp) ) select *,lag (session_count) over(order by date) previous_cnt , cast ( 100.00*(session_count-lag (session_count, 1,session_count) over(order by date) ) /lag (session_count) over(order by date) as decimal (10,2))increase_percent from q4; --(we can also use join) --q5--> user have highest session score --col-->date user name score --withou grouping base on same date score select login_timestamp, user_id, session_score from ( select *,max(SESSION_SCORE) over( partition by login_timestamp) mx from logins ) k where SESSION_SCORE=mx order by login_timestamp; --original with q4 as(select user_id,sum(SESSION_SCORE) sum , convert(date,login_timestamp) date from logins group by user_id,convert(date,login_timestamp) ) select date ,user_id,sum from (select *, dense_rank() over(partition by date order by sum desc) rnk from q4) k where rnk=1 order by date; --q6-->identify best user -- criteria--user have session in all day after first login(make assumption if needed) --col user_id with q6 as(select user_id, case when convert(date,dateadd(day ,-1, login_timestamp) )=convert(date,lag(login_timestamp,1 ,dateadd(day, -1,login_timestamp)) over(partition by user_id order by login_timestamp) ) then 0 else 1 end as flag from logins) select user_id from q6 group by user_id having sum(flag)=0; -- q7 at what date there is no login at all WITH cal AS ( SELECT convert (date,MIN(login_timestamp)) AS date, MAX(login_timestamp) AS lmt FROM logins UNION ALL SELECT DATEADD(day, 1, date), lmt FROM cal WHERE DATe < convert (date,lmt) ) SELECT date FROM cal a left join logins b on a.date =convert(date, b.login_timestamp) group by date having count(b.user_id) =0 order by date OPTION (MAXRECURSION 0) ; -- Create tables 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'); -- Insert values into 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;
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear