ADVANCED JOIN QUESTION | CALCULATE working percentage of a taxi company | Leetcode hard question

 Create table If Not Exists Drivers (driver_id int, join_date date)

Create table If Not Exists Rides (ride_id int, user_id int, requested_at date)

Create table If Not Exists AcceptedRides (ride_id int, driver_id int, ride_distance int, ride_duration int)

Truncate table Drivers

insert into Drivers (driver_id, join_date) values ('10', '2019-12-10')

insert into Drivers (driver_id, join_date) values ('8', '2020-1-13')

insert into Drivers (driver_id, join_date) values ('5', '2020-2-16')

insert into Drivers (driver_id, join_date) values ('7', '2020-3-8')

insert into Drivers (driver_id, join_date) values ('4', '2020-5-17')

insert into Drivers (driver_id, join_date) values ('1', '2020-10-24')

insert into Drivers (driver_id, join_date) values ('6', '2021-1-5')

Truncate table Rides

insert into Rides (ride_id, user_id, requested_at) values ('6', '75', '2019-12-9')

insert into Rides (ride_id, user_id, requested_at) values ('1', '54', '2020-2-9')

insert into Rides (ride_id, user_id, requested_at) values ('10', '63', '2020-3-4')

insert into Rides (ride_id, user_id, requested_at) values ('19', '39', '2020-4-6')

insert into Rides (ride_id, user_id, requested_at) values ('3', '41', '2020-6-3')

insert into Rides (ride_id, user_id, requested_at) values ('13', '52', '2020-6-22')

insert into Rides (ride_id, user_id, requested_at) values ('7', '69', '2020-7-16')

insert into Rides (ride_id, user_id, requested_at) values ('17', '70', '2020-8-25')

insert into Rides (ride_id, user_id, requested_at) values ('20', '81', '2020-11-2')

insert into Rides (ride_id, user_id, requested_at) values ('5', '57', '2020-11-9')

insert into Rides (ride_id, user_id, requested_at) values ('2', '42', '2020-12-9')

insert into Rides (ride_id, user_id, requested_at) values ('11', '68', '2021-1-11')

insert into Rides (ride_id, user_id, requested_at) values ('15', '32', '2021-1-17')

insert into Rides (ride_id, user_id, requested_at) values ('12', '11', '2021-1-19')

insert into Rides (ride_id, user_id, requested_at) values ('14', '18', '2021-1-27')

Truncate table AcceptedRides

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('10', '10', '63', '38')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('13', '10', '73', '96')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('7', '8', '100', '28')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('17', '7', '119', '68')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('20', '1', '121', '92')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('5', '7', '42', '101')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('2', '4', '6', '38')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('11', '8', '37', '43')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('15', '8', '108', '82')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('12', '8', '38', '34')

insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('14', '1', '90', '74')




select * from Drivers

select * from Rides

Select * FROM AcceptedRides


month | working_percentage |

+-------+--------------------+

| 1     | 0.00               |

| 2     | 0.00               |

| 3     | 25.00              |

| 4     | 0.00               |

| 5     | 0.00               |

| 6     | 20.00              |

| 7     | 20.00              |

| 8     | 20.00              |

| 9     | 0.00               |

| 10    | 0.00               |

| 11    | 33.33              |

| 12    | 16.67         



working_precentage = No. of drivers that accepted atleast one ride during the month/No. of available drivers in that month

Comments

Post a Comment

Popular posts from this blog

50 Essential SQL Questions to Land Your Dream Job

 CREATE TABLE worker (     first_name VARCHAR(50),     last_name VARCHAR(50),     department VARCHAR(50),     salary DECIMAL(10, 2),     joining_date DATE ); INSERT INTO worker (first_name, last_name, department, salary, joining_date) VALUES  ('John', 'Doe', 'HR', 55000, '2020-01-15'), ('Jane', 'Smith', 'Finance', 67000, '2019-03-22'), ('Robert', 'Johnson', 'IT', 72000, '2018-06-10'), ('Emily', 'Davis', 'Marketing', 59000, '2021-07-01'), ('Michael', 'Wilson', 'Sales', 63000, '2020-09-15'), ('Sarah', 'Brown', 'HR', 56000, '2022-02-19'), ('David', 'Miller', 'Finance', 68000, '2019-10-05'), ('Laura', 'Garcia', 'IT', 75000, '2020-11-22'), ('James', 'Martinez', 'Marketing', 60000, '2021-03-18'), ('Olivia...
Read more

How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

  DML Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( customer_name VARCHAR(255), order_date DATETIME ); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-05'); INSER...
Read more

ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

 DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Product VARCHAR(255),     Category VARCHAR(100) ); INSERT INTO Products (ProductID, Product, Category) VALUES     (1, 'Laptop', 'Electronics'),     (2, 'Smartphone', 'Electronics'),     (3, 'Tablet', 'Electronics'),     (4, 'Headphones', 'Accessories'),     (5, 'Smartwatch', 'Accessories'),     (6, 'Keyboard', 'Accessories'),     (7, 'Mouse', 'Accessories'),     (8, 'Monitor', 'Accessories'),     (9, 'Printer', 'Electronics');
Read more