We’ve analyzed over 50,000 interviews from pre-IPO to Fortune 500 companies at Big Tech Interviews (BTI) to curate an exclusive list of the latest SQL interview questions and solutions so you can ace your next interview!
You’re about to read the 10 latest SQL questions and solutions asked by Meta (Facebook) for data science, data analyst, business intelligence engineer, data engineer, and business analyst positions.
Want to practice more SQL interview questions like this with on-demand support and a dynamic SQL playground? Learn more here!
Meta/Facebook SQL Interview Question 10: Two different products
Prompt: Write a query that returns the name of brands that have at least two different products and its average price is greater than $3. Return the result order by brand name.
products table:
+----------+----------------+----------+------------+-----+
|product_id|product_class_id|brand_name|product_name|price|
+----------+----------------+----------+------------+-----+
|1 |1 |brand_1 |product_1 |4 |
|2 |1 |brand_2 |product_2 |2 |
|3 |2 |brand_2 |product_3 |9 |
|4 |2 |brand_3 |product_4 |1 |
|5 |3 |brand_3 |product_5 |5 |
+----------+----------------+----------+------------+-----+For this Meta (Facebook) problem, we need to aggregate by brand name and look for the ones that have count(distinct product id) greater or equal than 2, and whose average(price) is greater than 3. This requires basic knowledge on how group by works.
Solution
select p.brand_name
from products p
group by p.brand_name
having avg(p.price) > 3 and count(distinct p.product_id) >= 2
order by p.brand_name;Meta/Facebook SQL Interview Question 11: Promotion effectiveness
Prompt: Write a query to find what percent of orders had a valid promotion applied. Please round the results 2 decimal places.
Table: orders +----------+--------+------------+-----------+----------+ |product_id|store_id|promotion_id|store_sales|units_sold| +----------+--------+------------+-----------+----------+ |1 |10 |100 |1000 |1 | |1 |10 |200 |null |2 | |1 |10 |300 |1001 |3 | |1 |10 |400 |1002 |3 | |2 |10 |500 |1003 |3 | |2 |10 |600 |null |2 | |3 |10 |700 |null |4 | |3 |10 |800 |null |2 | |3 |20 |900 |null |1 | |3 |20 |100 |1004 |1 | |4 |20 |200 |1005 |1 | |4 |20 |300 |1006 |4 | |4 |20 |400 |1006 |4 | |5 |20 |500 |1002 |2 | +----------+--------+------------+-----------+----------+
Table: promotions +------------+ |promotion_id| +------------+ |1001 | |1002 | |1003 | |1004 | |1005 | |1006 | +------------+
The table orders contain the total orders, and the table promotions contain the valid promotions.
The first thing that we need to know is whether or not an order is related to a promotion, so in order to know that, we need to join both tables, but what type of join do we need? Since we need all the records from the orders table, and from the promotions only those that are related to the first table, then we need to use a left join.
Once we perform the join, the records with null value in the promotion_id column from the promotions table, will be the ones without promotions.
Then, to count the values with a valid promotion we need to perform count(p.promotion_id) (remember that the count function ignores the nulls), and to count the total of orders, we only need to use count(1), then the answer follows
Solution
select
round(1.0 * count(p.promotion_id) / count(1),2) as pct_with_promo
from orders o
left join promotions p on o.promotion_id = p.promotion_id;Meta/Facebook SQL Interview Question 30: Get the percentage of interactions
Prompt: Write a query to get the percentage of over five interactions.
Table: interactions
+--------+------+----------------+
|user_a |user_b|interaction_date|
+--------+------+----------------+
|1 |2 |current_date - 1|
|5 |2 |current_date - 1|
|5 |1 |current_date - 1|
|3 |5 |current_date - 1|
|2 |3 |current_date - 1|
|4 |2 |current_date - 1|
|4 |5 |current_date - 1|
|3 |4 |current_date - 1|
|3 |6 |current_date - 1|
|6 |5 |current_date - 1|
|4 |6 |current_date - 1|
|3 |1 |current_date - 1|
+--------+-----------------------+Step 1: Starting with the innermost Select statements we must turn the columns “user a” and “user b” into one row. This is based on the assumption: A user can both be in “user a” and “user b” and it is illegal for interaction containing the same two users to happen on the same day.
We also only care about the interactions yesterday. So, to make two columns into one we will use the UNION ALL function that combines two SELECT statements.
So we will write two SELECT statements: One that Selects all users in “user a” and names the column “user” (using AS) FROM our Interactions table that have the date of yesterday’s date using a WHERE command (we can get this using current_date-1).
SELECT "user_a" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
Then we make a copy of our previous statement, leaving a space in between, and edit it slightly by changing “user_a” to “user_b”
This gives us
SELECT "user_a" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
SELECT "user_b" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
Then all we need to do is add UNION ALL in the gap we left
SELECT "user_a" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
UNION ALL
SELECT "user_b" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
Step 2: Now we have a column containing the name of each user a number of times equal to the amount of interactions they were in yesterday. We now want to COUNT how many times the name of a user appears in the column.
We will do this by starting a new SELECT line about our code. We will SELECT the “user” column we just defined, and COUNT(*) ( * means all) which will be defined after our Step 1 Code.
We want to specify that we are using what we selected in Step 1. To do this we put a FROM and open parentheses right before our Step 1 code, and a close parenthesis after it. We also have to name our code from Step 1 so we will use AS to name it Step_1.
SELECT "user", COUNT(*) as "count"
FROM
(
SELECT "user_a" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
UNION ALL
SELECT "user_b" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
) AS Step_1
The name is not important but is needed to execute. We now want to specify what we are counting, so, we will use the GROUP BY function to say what we are counting by. In this case we are counting by “user” So we put GROUP BY “user” on the next line.
We also want to specify to only include the users with a count of 5 or more. We will do this with HAVING COUNT(*) >= 5
SELECT "user", COUNT(*) as "count"
FROM
(
SELECT "user_a" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
UNION ALL
SELECT "user_b" AS "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
) AS Step_1
GROUP BY “user”
HAVING COUNT(*) >= 5
Step 3: Now all we need to do is count how many rows we have. This step is very similar to Step 2.
Add a new SELECT line before all our code. SELECT for COUNT(*). Write FROM( in between our code in Step2 and our new SELECT statement and then close the parenthesis after our entine code from Step 2 and name it anything (in this case we will name it Step2)
Solution
SELECT COUNT(*) as "Over_Five_Interactions"
FROM(
SELECT "user", COUNT(*) as "count"
FROM
(
SELECT "user_a" as "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
UNION ALL
SELECT "user_b" as "user"
FROM Interactions
WHERE "interaction_date" = current_date - 1
) AS X
GROUP BY "user"
HAVING COUNT(*) >= 5
) AS Y
After that we compile and it will show us the number of Users that had 5 or more interactions yesterday!
Meta/Facebook SQL Interview Question 13: Find customers who bought two brands
Prompt: Write a query to find out which customers have bought products from both the “brand1” and the “brand2” brands.
Table: orders +----------+--------+------------+-----------+----------+ |product_id|store_id|promotion_id|store_sales|units_sold| +----------+--------+------------+-----------+----------+ |1 |10 |100 |1000 |1 | |1 |10 |200 |null |2 | |1 |10 |300 |1001 |3 | |1 |10 |400 |1002 |3 | |2 |10 |500 |1003 |3 | |2 |10 |600 |null |2 | |3 |10 |700 |null |4 | |3 |10 |800 |null |2 | |3 |20 |900 |null |1 | |3 |20 |100 |1004 |1 | |4 |20 |200 |1005 |1 | |4 |20 |300 |1006 |4 | |4 |20 |400 |1006 |4 | |5 |20 |500 |1002 |2 | +----------+--------+------------+-----------+----------+
Table: products+----------+----------------+----------+------------+-----+ |product_id|product_class_id|brand_name|product_name|price| +----------+----------------+----------+------------+-----+ |1 |1 |brand_1 |product_1 |4 | |2 |1 |brand_2 |product_2 |2 | |3 |2 |brand_2 |product_3 |9 | |4 |2 |brand_3 |product_4 |1 | |5 |3 |brand_3 |product_5 |5 | +----------+----------------+----------+------------+-----+Table: customers +------------+----------+---------+ | customer_id|first_name|last_name| +------------+----------+---------+ |100 |A |J | |200 |B |K | |300 |C |L | |400 |D |M | |500 |E |N | |600 |F |O | |700 |G |P | |800 |H |Q | |900 |I |R | +------------+----------+---------+
First, let’s create a query that returns the different customer who has bought products from brand1 and brand2:
select distinct
c.customer_id,
c.first_name,
c.last_name,
p.brand_name
from orders o
left join customers c on o.customer_id = c.customer_id
left join products p on o.product_id = p.product_id
where p.brand_name in ('brand_1', 'brand_2');
Since we are using a select distinct, each customer has three options: If haven’t bought the brands, then the customer won’t be in the table If have bought only from one brand, they’ll have one record If have bought from both brands, they’ll have two record Having said so, to determine which customers have bought from both brands, we need to select from this table the customers who have exactly two records.
Then, by using a CTE, the answer will be:
Solution
with required_brands as (
select distinct
c.customer_id,
c.first_name,
c.last_name,
p.brand_name
from orders o
left join customers c on o.customer_id = c.customer_id
left join products p on o.product_id = p.product_id
where p.brand_name in ('brand_1', 'brand_2')
)
select customer_id, first_name, last_name
from required_brands
group by customer_id, first_name, last_name
having count(distinct brand_name) = 2
order by first_name, last_name;Want to see more Meta (Facebook) SQL questions and interview guides for data science, data analyst, business analyst, and data engineer roles? Check out our recent Meta guide here!
Meta/Facebook SQL Interview Question 23: Showcase the current post rate
Prompt: Write a query to showcase the current post rate.
Note: Please round the results 2 decimal places.
Table: actions
+-------+-------+-------------------------+
|user_id|action |date |
+-------+-------+-------------------------+
|1 |post |current_timestamp::DATE-3|
|2 |edit |current_timestamp::DATE-2|
|3 |post |current_timestamp::DATE-1|
|4 |post |current_timestamp::DATE-1|
|5 |edit |current_timestamp::DATE-5|
|6 |cancel |current_timestamp::DATE-2|
|7 |post |current_timestamp::DATE-2|
|8 |post |current_timestamp::DATE-1|
|9 |post |current_timestamp::DATE-1|
|10 |cancel |current_timestamp::DATE-3|
|11 |post |current_timestamp::DATE-2|
|12 |post |current_timestamp::DATE-2|
+-------+-------+-------------------------+We need to count the records whose action column is equal to post, and then divide over the total number of records. There is a common trick to do so:
Solution
select
round(1.0 * sum(
case
when action = 'post' then 1
else 0
end) /
count(1),2) as post_rate
from actions;Meta/Facebook SQL Interview Question 14: Users with more than 10 searches
Prompt: Write a query to show how many users made more than 10 searches in the last 7 days.
products searches:
+-----------+---------+-------+---------+-------------+
|search_date|search_id|user_id|age_group|search_query |
+-----------+---------+-------+---------+-------------+
|2022-06-01 |101 |9991 |<30 |justin bieber|
|2022-06-01 |102 |9991 |<30 |menlo park |
|2022-05-01 |103 |5555 |30-50 |john |
|2022-04-01 |104 |1234 |50+ |funny cats |
+-------+-----------+---------+---------+-------------+First, we need to calculate the number of searches per user in the last seven days.
select user_id, count(1) as num_searches
from searches
where search_date >= (current_timestampt::date - 6)
group by user_id
Then, count from there the number of users whose number of searches is greater than 10.
Solution
with last_searches as (
select user_id, count(1) as num_searches
from searches
where search_date >= (current_date - 6)
group by user_id
)
select count(user_id) as result
from last_searches
where num_searches > 10Meta/Facebook SQL Interview Question 21: Number of likes made on friend posts
Prompt: Write a query to find the number of likes on a friend’s posts from yesterday.
Table: post_relationship
+-------------------------+---------+-------------+------------+
|date |post_id |relationship |interaction |
+-------------------------+---------+-------------+------------+
|current_timestamp::DATE-3|1 |Friend |share |
|current_timestamp::DATE-2|1 |Group |like |
|current_timestamp::DATE-1|2 |Group |post |
|current_timestamp::DATE-1|2 |Page |like |
|current_timestamp::DATE-5|2 |Page |like |
|current_timestamp::DATE-1|3 |Group |like |
|current_timestamp::DATE-1|3 |Friend |like |
|current_timestamp::DATE-1|4 |Page |like |
+-------------------------+---------+-------------+------------+Here we just simply need to filter where data equals yesterday, interaction equals like and relationship equals Friend and then count
Solution
select count(post_id)
from post_relationship
where interaction = 'like'
and relationship = 'Friend'
and date = current_timestamp::date-1;Meta/Facebook SQL Interview Question 22: Average number of likes on posts
Prompt: Write a query on the average number of likes on posts for when poster_id = 101.
Table: post_relationship +-------------------------+---------+-------------+------------+ |date |post_id |relationship |interaction | +-------------------------+---------+-------------+------------+ |current_timestamp::DATE-3|1 |Friend |share | |current_timestamp::DATE-2|1 |Group |like | |current_timestamp::DATE-1|2 |Group |post | |current_timestamp::DATE-1|2 |Page |like | |current_timestamp::DATE-5|2 |Page |like | |current_timestamp::DATE-1|3 |Group |like | |current_timestamp::DATE-1|3 |Friend |like | |current_timestamp::DATE-1|4 |Page |like | +-------------------------+---------+-------------+------------+Table: posts +-------+---------+ |post_id|poster_id| +-------+---------+ |1 |101 | |2 |101 | |3 |102 | |4 |103 | +-------+---------+
First, we need to calculate how many likes there were by post for the poster_id = 101. To do this we need to filter by posterid, interaction, and aggregate by post_id
select
p.post_id,
count(1) num_likes
from post_relationship pr
join posts p on pr.post_id = p.post_id
where p.poster_id = 101and pr.interaction = 'like'
group by p.post_id;
Once we have done this, we only need to calculate the average of the num_likes column
Solution
with likes_per_post as (
select
p.post_id,
count(1) num_likes
from post_relationship pr
join posts p on pr.post_id = p.post_id
where p.poster_id = 101 and pr.interaction = 'like'
group by p.post_id
)
select avg(num_likes) from likes_per_postMeta/Facebook SQL Interview Question 1: Products with no sales
Prompt: Write a query to return the product groups in the US that have no sales of any unit.
orders table:
+-------------+---------+-------------+--------------------+
| order_id | item_id | quantity | order_date |
+-------------+---------+-------------+--------------------+
| 1 | 46 | 646 | 2019-01-01 9:00:00 |
| 2 | 63 | 343 | 2019-01-01 12:00:00|
| 3 | 23 | 987 | 2019-01-02 13:00:00|
| 4 | 24 | 234 | 2019-01-01 17:00:00|
| 5 | 53 | 854 | 2019-01-03 23:00:00|
+-------------+---------+-------------+---------+----------+
catalog table:
+---------+-------------+---------------+---------------+
| item_id | product_id | product_group | location |
+---------+-------------+---------------+---------------+
| 46 | 543 | A | US |
| 63 | 567 | B | US |
| 23 | 566 | B | MX |
| 24 | 563 | C | CA |
| 53 | 568 | D | US |
+---------+-------------+---------------+---------------+Solution
SELECT catalog.product_group
FROM catalog
WHERE NOT EXISTS
(SELECT 1
FROM orders
WHERE orders.item_id = catalog.item_id )
GROUP BY catalog.product_group;Meta/Facebook SQL Interview Question 25: Ratio of Posts
Prompt: Write a query to showcase what the current post rate is.
Note: Please round the results to 2 decimal places.
Table: events
+-------+--------------------------+--------+-----+-----------+
|user_id|date |post_id |type |application|
+-------+--------------------------+--------+-----+-----------+
|1 |current_timestamp::DATE-5 |101 |post |twitter |
|2 |current_timestamp::DATE-1 |102 |edit |facebook |
|3 |current_timestamp::DATE-4 |105 |post |twitter |
|4 |current_timestamp::DATE-1 |109 |like |facebook |
|5 |current_timestamp::DATE-1 |102 |post |facebook |
|6 |current_timestamp::DATE-3 |107 |post |snapchat |
|7 |current_timestamp::DATE-4 |110 |edit |facebook |
|8 |current_timestamp::DATE-5 |111 |post |facebook |
|9 |current_timestamp::DATE-1 |112 |like |instagram |
|10 |current_timestamp::DATE-1 |115 |post |instagram |
|11 |current_timestamp::DATE-1 |117 |post |facebook |
|12 |current_timestamp::DATE-5 |118 |post |facebook |
|13 |current_timestamp::DATE-1 |119 |like |instagram |
|14 |current_timestamp::DATE-1 |120 |post |instagram |
|15 |current_timestamp::DATE-1 |121 |post |facebook |
+-------+--------------------------+--------+-----+-----------+First, we need to filter by date equals yesterday, then we need to count the records whose type column is equal to post, and then divide over the total number of records, and then group by application.
Solution
select
application,
round(1.0 * sum(
case
when type = 'post' then 1
else 0
end) /
count(1),2)
from events
where date = current_timestamp::DATE-1
group by applicationWant to practice more questions like this with on-demand support and a dynamic SQL playground? Learn more here!
We’ve analyzed over 50,000 interviews from the latest data science, data analyst, data engineer, business analyst, and business intelligence engineer roles being filled by pre-IPO to Fortune 500 companies at Big Tech Interviews (BTI) to curate an exclusive list of the latest SQL interview questions and solutions so you can ace your next interview!
