Q1. Given the customer_purchases and product_list tables, Write a query to calculate the year-over-year growth rate of the amount spent by each customer.
Table structure:
CREATE TABLE customer_purchases ( Customer_id VARCHAR, Product_id VARCHAR, Purchase_amount DECIMAL(10,2), Purchase_date DATE );
CREATE TABLE product_list ( Product_id VARCHAR PRIMARY KEY, Product_name VARCHAR, Category_name VARCHAR );
Q2. Given a supplier_inventory table with daily records of products supplied, identify the days when the stock went below a threshold for more than two consecutive days.
Table structure:
CREATE TABLE supplier_inventory ( Supplier_id VARCHAR, Product_id VARCHAR, Stock_quantity INT, Record_date DATE );
Required: Write an SQL query to select the Supplier_id, Product_id, and start date of the period when the stock quantity was below 50 units for more than two consecutive days.
Q3. Given employee_sales and store_performance, rank the employees within each store by their sales performance in the current year.
Table structure:
CREATE TABLE employee_sales ( Employee_id VARCHAR, Store_id VARCHAR, Sale_amount DECIMAL(10,2), Sale_year INT );
CREATE TABLE store_performance ( Store_id VARCHAR PRIMARY KEY, Store_name VARCHAR, Annual_target DECIMAL(10,2) );
Required: Write an SQL query to select the Employee_id, Store_id, and a rank based on their Sale_amount for the year 2023, with 1 being the highest performing employee.
Q4. Given passenger_flights and flight_details, find passengers who have taken more than 10 flights within the last year from the same airport.
Table structure:
CREATE TABLE passenger_flights ( Passenger_id VARCHAR, Flight_id VARCHAR, Departure_date DATE );
CREATE TABLE flight_details ( Flight_id VARCHAR PRIMARY KEY, Departure_airport_code VARCHAR, Arrival_airport_code VARCHAR );
Required: Write an SQL query to select the Passenger_id and Departure_airport_code for passengers with more than 10 flights from the same airport since last year.
Join Swiggy – https://careers.swiggy.com/#/
You can also connect with us on all our social media platforms. Join us for exploring best career opportunities in all over the globe, so like, share, and subscribe to our pages.
For the Latest Jobs Alerst, You can also connect with us on all our social media platforms.
Join us for exploring the best career opportunities all over the globe, Please like, share, and subscribe to our pages.
- Linktree: https://linktr.ee/jobconnections.agency
- Facebook: https://www.facebook.com/people/Job-Connections/61550826344327/
- LinkedIn: https://www.linkedin.com/company/jobconnectionsagency
- Youtube: https://www.youtube.com/@JobConnectionsagency
- X: https://twitter.com/JobConnections0
- Telegram: https://t.me/jobconnectionsagency
- Instagram: https://www.instagram.com/jobconnections.agency
- Whatsapp: https://whatsapp.com/channel/0029VaAIPxAGOj9wHa1r6M19