@Roopesh122

/* Tesla SQL Interview Question The company you are working for wants to anticipate their staffing needs by identifying their top two busiest times of the week. To find this, each day should be segmented into differents parts using following criteria: Morning: Before 12 p.m. (not inclusive) Early afternoon: 12 -15 p.m. Late afternoon: after 15 p.m. (not inclusive) Your output should include the day and time of day combination for the two busiest times, i.e. the combinations with the most ord

/* Amazon, Doordash & Bosch SQL Interview Question Find all employees who have or had a job title that includes manager. Output the first name along with the corresponding title. */ -- Schema Setup CREATE TABLE workers (department VARCHAR(100), first_name VARCHAR(50), joining_date DATE, last_name VARCHAR(50), salary BIGINT, worker_id BIGINT PRIMARY KEY);

/* Meta SQL Interview Question Market penetration is an important metric for Spotify's growth in different regions. As part of the analytics team, calculate the active user penetration rate in specific countries. Active Users must meet these criteria: Interacted with Spotify within the last 30 days (last_active_date >= 2024-01-01). At least 5 sessions. At least 10 listening hours. Formula: Active User Penetration Rate = (Number of Active Spotify Users in the Country / Total Users in the Count

/* Expedia, Airbnb & Tripadvisor SQL Interview Question Find the top two hotels with the most negative reviews. Output the hotel name along with the corresponding number of negative reviews. Negative reviews are all the reviews with text under negative review different than "No Negative". Sort records based on the number of negative reviews in descending order. */ -- Schema Setup

/* EY, TCS & Deloitte SQL Interview Question In a marathon, gun time is counted from the moment of the formal start of the race while net time is counted from the moment a runner crosses a starting line. Both variables are in seconds. You are asked to check if the interval between the two times is different for male and female runners. First, calculate the average absolute difference between the gun time and net time. Group the results by available genders (male and female). Output the absolu

/* Amazon SQL Interview Question Find the total number of people present inside the hospital */ -- Schema Setup create table hospital ( emp_id int , action varchar(10) , time datetime);

/* Microsoft SQL Interview Question Write a query that returns the company (customer id column) with highest number of users that use desktop only. */ -- Schema Setup CREATE TABLE fact_events (id INT PRIMARY KEY, time_id DATETIME, user_id VARCHAR(50), customer_id VARCHAR(50), client_id VARCHAR(50), event_type VARCHAR(50), event_id INT); INSERT INTO fact_events (id, time_id, user_id, customer_id, client_id, event_type, event_id) VALUES (1, '2024-12-01 10:00:00', 'U1', 'C1', 'desktop', 'click

/* Visa SQL Interview Question Identify the top 3 areas with the highest customer density. Customer density = (total number of unique customers in the area / area size). Your output should include the area name and its calculated customer density. */ -- Schema Setup CREATE TABLE transaction_records (customer_id BIGINT, store_id BIGINT, transaction_amount BIGINT, transaction_date DATETIME, transaction_id BIGINT PRIMARY KEY);

/* Amazon SQL Interview Question You are given the table with titles of recipes from a cookbook and their page numbers. You are asked to represent how the recipes will be distributed in the book. Produce a table consisting of three columns: left_page_number, left_title and right_title. The k-th row (counting from 0), should contain the number and the title of the page with the number 2×k in the first and second columns respectively, and the title of the page with the number 2×k+1 in the thir

/* Amazon SQL Interview Question Find the best selling item for each month (no need to separate months by year) where the biggest total invoice was paid. The best selling item is calculated using the formula (unitprice * quantity). Output the month, the description of the item along with the amount paid. */ -- Schema Setup CREATE TABLE online_retail (invoiceno VARCHAR(50),stockcode VARCHAR(50),description VARCHAR(255),quantity INT,invoicedate DATETIME,unitprice FLOAT,customerid FLOAT,country

/* Airbnb SQL Interview Question Estimate the growth of Airbnb each year using the number of hosts registered as the growth metric. The rate of growth is calculated by taking ((number of hosts registered in the current year - number of hosts registered in the previous year) / the number of hosts registered in the previous year) * 100. Output the year, number of hosts in the current year, number of hosts in the previous year, and the rate of growth. Round the rate of growth to the nearest perce

/* Uber SQL Interview Question Find the most profitable location. Write a query that calculates the average signup duration and average transaction amount for each location, and then compare these two measures together by taking the ratio of the average transaction amount and average duration for each location. Your output should include the location, average duration, average transaction amount, and ratio. Sort your results from highest ratio to lowest. */ -- Schema Setup

/* Google, Airbnb & Expedia SQL Interview Question Find the three ten hotels with the highest ratings. Output the hotel name along with the corresponding average score. Sort records based on the average score in descending order. */ -- Schema Setup CREATE TABLE hotel_address (hotel_address VARCHAR(255),additional_number_of_scoring INT,review_date DATETIME,average_score FLOAT,hotel_name VARCHAR(255),reviewer_nationality VARCHAR(50),negative_review VARCHAR(100),review_total_negative_word_counts

/* Accenture SQL Interview Question Following a recent advertising campaign, the marketing department wishes to classify its efforts based on the total number of units sold for each product. You have been tasked with calculating the total number of units sold for each product and categorizing ad performance based on the following criteria for items sold: Outstanding: 30+ Satisfactory: 20 - 29 Unsatisfactory: 10 - 19

/* Google SQL Interview Question Calculate the average session distance traveled by Google Fit users using GPS data for two scenarios: Considering Earth's curvature (Haversine formula). Assuming a flat surface. For each session, use the distance between the highest and lowest step IDs, and ignore sessions with only one step. Calculate and output the average distance for both scenarios and the difference between them. Formulas: 1. Curved Earth: d=6371×arccos(sin(ϕ1)×sin(ϕ2)+cos(ϕ1)×cos(ϕ2)×c

/* Spotify SQL Interview Question Find the number of days a US track has stayed in the 1st position for both the US and worldwide rankings on the same day. Output the track name and the number of days in the 1st position. Order your output alphabetically by track name. If the region 'US' appears in dataset, it should be included in the worldwide ranking */ -- Schema Setup CREATE TABLE spotify_daily_rankings_2017_us (position INT,trackname VARCHAR(255),artist VARCHAR(255),streams INT,url VARCH

/* Amazon SQL Interview Question Given the users' sessions logs on a particular day, calculate how many hours each user was active that day. Note: The session starts when state = 1 and ends when state = 0. */ -- Schema Setup CREATE TABLE customer_state_log (cust_id VARCHAR(10),state INT,timestamp TIME);

/* GoldmanSachs & Deloitte SQL Interview Question You are given a day worth of scheduled departure and arrival times of trains at one train station. One platform can only accommodate one train from the beginning of the minute it's scheduled to arrive until the end of the minute it's scheduled to depart. Find the minimum number of platforms necessary to accommodate the entire scheduled traffic. */ -- Schema Setup CREATE TABLE train_arrivals (train_id INT, arrival_time DATETIME);

/* Meta & Salesforce SQL Interview Question Find the highest salary among salaries that appears only once. */ -- Schema Setup CREATE TABLE employee(id INT,first_name VARCHAR(50),last_name VARCHAR(50),age INT,sex VARCHAR(1),employee_title VARCHAR(50),department VARCHAR(50),salary INT,target INT,bonus INT,email VARCHAR(100),city VARCHAR(50),address VARCHAR(100),manager_id INT); INSERT INTO employee (id, first_name, last_name, age, sex, employee_title, department, salary, target, bonus, email,

/* Linkedin SQL Interview Question Consider all LinkedIn users who, at some point, worked at Microsoft. For how many of them was Google their next employer right after Microsoft (no employers in between)? */ -- Schema Setup CREATE TABLE linkedin_users (user_id INT,employer VARCHAR(255),position VARCHAR(255),start_date DATETIME,end_date DATETIME); INSERT INTO linkedin_users (user_id, employer, position, start_date, end_date) VALUES(1, 'Microsoft', 'developer', '2020-04-13', '2021-11-01'),(1,