1) Self Introduction
(Project Based Questions)
1) Explained about project
2)) How you extract Data from API after that How you transform it to structure format from JSON data
3) What are the challenges you faced in this project and extarcting data from API and how did you overcome it
(Power BI Scenario Based Question )
1) What are the visuals mainly used for your projects give explanation about it ?
2) Let assume you have 1000 products and have column of price_2020,price_2021,price_2022
which visualization you used to show consectuive increase or decrease price for each product
3) Which visualization used to show decreasing data can show based upon previous month ?
(SQL Based Question)
1) /*Write a solution to find the IDs of the users who visited without making any transactions and
the number of times they made these types of visits. */
CREATE TABLE Visits (
visit_id INT PRIMARY KEY,
customer_id INT
);
INSERT INTO Visits (visit_id, customer_id) VALUES
(1, 23),
(2, 9),
(4, 30),
(5, 54),
(6, 96),
(7, 54),
(8, 54);
CREATE TABLE Transactions (
transaction_id INT PRIMARY KEY,
visit_id INT,
amount DECIMAL(10, 2),
FOREIGN KEY (visit_id) REFERENCES Visits(visit_id)
);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES
(2, 5, 310),
(3, 5, 300),
(9, 5, 200),
(12, 1, 910),
(13, 2, 970);
EXPECTED OUTPUT :-
Customer_id No_of_Visited
30 1
54 2
96 1
2)
/*WAQ to find how many times the prices of a particular product has increased in consecutive years. */
CREATE TABLE products (
product_id INT PRIMARY KEY,
price_2020 DECIMAL(10, 2),
price_2021 DECIMAL(10, 2),
price_2022 DECIMAL(10, 2)
);
INSERT INTO products (product_id, price_2020, price_2021, price_2022) VALUES
(1098, 7800, 7800, 7600),
(1080, 3400, 3500, 3500),
(1076, 2300, 2400, 2500),
(1054, 1200, 1200, NULL),
(1154, 8900, 8900, 9000);
expected output :-
product_id No_of_increased
1076 2
1080 1
1154 1
3) /*Write a solution to find the cancellation rate of requests with unbanned users
(both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points. */
CREATE TABLE trips (
id INT PRIMARY KEY,
client_id INT,
driver_id INT,
city_id INT,
Ticket_uuid INT,
status VARCHAR(50),
Create_timestamp DATETIME
);
INSERT INTO trips (id, client_id, driver_id, city_id, Ticket_uuid, status, Create_timestamp) VALUES
(1, 1, 10, 1, 11, 'completed', '2013-10-01 11:30:20'),
(2, 2, 11, 1, 21, 'cancelled_by_driver', '2013-10-01 12:31:10'),
(3, 3, 12, 6, 43, 'completed', '2013-10-01 01:01:10'),
(4, 4, 13, 6, 90, 'cancelled_by_client', '2013-10-01 09:31:56'),
(5, 1, 10, 1, 12, 'completed', '2013-10-02 13:45:52'),
(6, 2, 11, 6, 67, 'completed', '2013-10-02 19:18:34'),
(7, 3, 12, 6, 54, 'completed', '2013-10-02 21:43:19'),
(8, 2, 12, 12, 89, 'completed', '2013-10-03 23:19:19'),
(9, 3, 10, 12, 78, 'completed', '2013-10-03 20:20:20'),
(10, 4, 13, 12, 87, 'cancelled_by_driver', '2013-10-03 01:30:45');
CREATE TABLE users (
users_id INT PRIMARY KEY,
banned VARCHAR(3),
role VARCHAR(10)
);
INSERT INTO users (users_id, banned, role) VALUES
(1, 'No', 'client'),
(2, 'Yes', 'client'),
(3, 'No', 'client'),
(4, 'No', 'client'),
(10, 'No', 'driver'),
(11, 'No', 'driver'),
(12, 'No', 'driver'),
(13, 'No', 'driver');
Expected_output :-
Dates Cancellation_rate
2013-10-01 0
2013-10-02 0
2013-10-03 0
