There are couple of SQL questions interview, which require some additional thought processing and including subqueries with medium complicated logic.
Facebook SQL Interview
Definition:
Given a table of Facebook posts, for each user who posted at least twice in 2021, write a query to find the number of days between each user’s first post of the year and last post of the year in the year 2021. Output the user and number of the days between each user’s first and last post.
Solution:
The issue is about getting the MAX() and MIN() — and define difference between those.
Filtering the data by year (2021) -> also operation with GROUP and HAVING shows results which necessary.
Twitter SQL Interview
Definition:
Assume you’re given a table Twitter tweet data, write a query to obtain a histogram of tweets posted per user in 2022. Output the tweet count per user as the bucket and the number of Twitter users who fall into that bucket.
In other words, group the users by the number of tweets they posted in 2022 and count the number of users in each group.
Solutions:
There is subquery for using — which using count(*) — to get how many users already post within time interval.
Tweet bucket getting the number of users — which post x tweet. Then counting how many users in tweet bucket grouping those.
Tiger Analytics Interview.
Problem Statement:
Given a database containing flight information, including unique flight IDs and corresponding origin and destination airports, write a SQL query to extract and display the origin and destination airports for all flights.
Solution:
Its about reducing the data — if there is flight with stop like:
Del -> Had -> Bar
Mum -> Agra -> Kol
Then displaying only first and end station. It can be done with inner join to the same table.
Similar query is:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;
Where its retrieve the names of both the employees and their respective managers in the result set.
BEC Poland Interview
Problem Statement:
Write a SQL query to find all the employee whose salary is same in same department.
Solution:
There is also subquery — grouped by dept_id, salary, with having count(1)=1)
“HAVING COUNT(1)=1” filters groups in SQL queries to only include those with exactly one row per group.
Then inner join by dept_id and salary print results
Amazon Interview
Problem Statement:
Write a SQL query to find the total number of people present inside the hospital.
Solution:
The issue is adding subquery which getting the maximum value of in and out (in case if someone go in and out more than 1 times).
Then if the Intime > OutTime mean that someone go outside, but return, and if outcome is null then don’t go outside.
Google Interview
Problem Statement:
Find the company who have at least 2 users who speaks English and German both the languages.
Solution:
The query need to check for English and German grouped by company id then by userId.
“HAVING COUNT(1) = 2” filters SQL query results to only include groups where the count of rows within each group is exactly two. — For both language
Then having count(1)=>2 — getting at least 2 users in company.
Larsen & Toubro L&T Interview
Problem Statement:
Print Highest and Lowest Salary Employees in Each Department
Solution:
There is selecting min() and max() in subquery grouped by dep_id.
Then this subquery used in changing the max_salary with emp_name, the same with min_salary.
With inner join on those data.
Swiggy Data Analyst SQL Interview Question
Problem Statement:
Write a query to find out supplier_id, product_id, and starting date of record_date for which stock quantity is less
than 50 for two or more consecutive days.
Solution:
Sure! Think of partitioning like dividing your data into groups based on certain criteria. The window function then works separately within each group, giving you results specific to each group.
This SQL query uses a Common Table Expression (CTE) named “cte” to:
- Calculate Previous Date: It uses the LAG function to find the previous record date for each combination of supplier and product.
- Calculate Day Difference: It calculates the difference in days between the current record date and the previous record date for each combination of supplier and product.
- Filter Data: It only includes rows where the “stock_quantity” is less than 50.
Then another subquery
- Create a new column called “group_flag”.
- If the difference in days between consecutive records (“daydiff”) is 1 or less, we set “group_flag” to 0; otherwise, it’s set to 1.
- Another new column, “group_id”, is created.
- This column assigns a unique ID to each group of consecutive records for each supplier and product combination.
- If the day difference between consecutive records is 1 or less, it sums up to 0, otherwise, it sums up to 1.
- It calculates this sum separately for each combination of supplier and product.
Then selecting the data, which are more than 2.
