Prime SQL Patterns from FAANG Knowledge Science Interviews (with Code)

Picture by Creator

# Introduction

 
The technical screening for knowledge science roles in FAANG corporations could be very thorough. Nonetheless, even they will’t give you an infinite stream of distinctive interview questions. When you’ve gone by the grind sufficient occasions, you begin to discover that some SQL patterns maintain exhibiting up.

Listed below are the highest 5, with examples and code (PostgreSQL) for observe.

Picture by Creator | Serviette AI

Grasp these and also you’ll be prepared for many SQL interviews.

# Sample #1: Aggregating Knowledge with GROUP BY

 
Utilizing mixture capabilities with GROUP BY permits you to mixture metrics throughout classes.

This sample is usually mixed with knowledge filtering, which suggests utilizing one of many two clauses:

• WHERE: Filters knowledge earlier than the aggregation.
• HAVING: Filters knowledge after the aggregation.

Instance: This Meta interview query asks you to search out the whole variety of feedback made 30 or fewer days earlier than 2020-02-10 per consumer. Customers with no feedback must be excluded from the output.

We use the SUM() perform with a GROUP BY clause to sum the variety of feedback per consumer. Outputting the feedback solely throughout the specified interval is achieved by filtering the info earlier than aggregation, i.e., utilizing WHERE. There’s no must calculate which date is “30 days earlier than 2020-02-10”; we merely subtract 30 days from that date utilizing the INTERVAL date perform.

SELECT user_id,
       SUM(number_of_comments) AS number_of_comments
FROM fb_comments_count
WHERE created_at BETWEEN '2020-02-10'::DATE - 30 * INTERVAL '1 day' AND '2020-02-10'::DATE
GROUP BY user_id;

Right here’s the output.
 

Enterprise Use:

• Person exercise metrics: DAU & MAU, churn charge.
• Income metrics: income per area/product/time interval.
• Person engagement: common session size, common clicks per consumer.

# Sample #2: Filtering with Subqueries

 
When utilizing subqueries for filtering, you create a knowledge subset, then filter the primary question in opposition to it.

The 2 principal subquery varieties are:

• Scalar subqueries: Return a single worth, e.g., most quantity.
• Correlated subqueries: Reference and rely on the results of the outer question to return the values.

Instance: This interview query from Meta asks you to create a suggestion system for Fb. For every consumer, you must discover pages that this consumer doesn’t comply with, however at the least certainly one of their buddies does. The output ought to encompass the consumer ID and the ID of the web page that must be advisable to this consumer.

The outer question returns all of the user-page pairs the place the web page is adopted by at the least one buddy.

Then, we use a subquery within the WHERE clause to take away the pages that the consumer already follows. There are two situations within the subquery: one which solely considers pages adopted by this particular consumer (checks for this consumer solely), after which checks if the web page thought-about for suggestion is amongst these adopted by the consumer (checks for this web page solely).

Because the subquery returns all of the pages adopted by the consumer, utilizing NOT EXISTS in WHERE excludes all these pages from the advice.

SELECT DISTINCT f.user_id,
                p.page_id
FROM users_friends f
JOIN users_pages p ON f.friend_id = p.user_id
WHERE NOT EXISTS
    (SELECT *
     FROM users_pages pg
     WHERE pg.user_id = f.user_id
       AND pg.page_id = p.page_id);

Right here’s the output.
 

Enterprise Use:

• Buyer exercise: most up-to-date login per consumer, newest subscription change.
• Gross sales: highest order per buyer, prime income order per area.
• Product efficiency: most bought product in every class, highest-revenue product monthly.
• Person behaviour: Longest session per consumer, first buy per buyer.
• Critiques & suggestions: prime reviewer, newest overview for every product.
• Operations: Newest cargo standing per order, quickest supply time per area.

# Sample #3: Rating with Window Features

 
Utilizing window capabilities reminiscent of ROW_NUMBER(), RANK(), and DENSE_RANK() permits you to order rows inside knowledge partitions, after which determine the primary, second, or nth document.

Here’s what every of those rating window capabilities does:

• ROW_NUMBER(): Assigns a novel sequential quantity inside every partition; tied values get totally different row numbers.
• RANK(): Assigns the identical rank to tied values and skips the subsequent ranks for the subsequent non-tied worth.
• DENSE_RANK(): Similar as RANK(), solely it doesn’t skip rank after ties.

Instance: In an Amazon interview query, we have to discover the very best each day order price between 2019-02-01 and 2019-05-01. If a buyer has multiple order on a sure day, sum the order prices every day. The output ought to include the client’s first title, the whole price of their order(s), and the date of the order.

Within the first frequent desk expression (CTE), we discover the orders between the required dates and sum the client’s each day totals for every date.

Within the second CTE, we use RANK() to rank prospects by order price descendingly for every date.

Now, we be part of two CTEs to output the required columns and filter solely the orders with the primary rank assigned to them, i.e., the very best order.

WITH customer_daily_totals AS (
  SELECT o.cust_id,
         o.order_date,
         SUM(o.total_order_cost) AS total_daily_cost
  FROM orders o
  WHERE o.order_date BETWEEN '2019-02-01' AND '2019-05-01'
  GROUP BY o.cust_id, o.order_date
),

ranked_daily_totals AS (
  SELECT cust_id,
         order_date,
         total_daily_cost,
         RANK() OVER (PARTITION BY order_date ORDER BY total_daily_cost DESC) AS rnk
  FROM customer_daily_totals
)

SELECT c.first_name,
       rdt.order_date,
       rdt.total_daily_cost AS max_cost
FROM ranked_daily_totals rdt
JOIN prospects c ON rdt.cust_id = c.id
WHERE rdt.rnk = 1
ORDER BY rdt.order_date;

Right here’s the output.
 

Enterprise Use:

• Person exercise: “Prime 5 most lively customers final month”.
• Income: “The second-highest income area”.
• Product reputation: “Prime 10 best-selling merchandise”.
• Purchases “The primary buy of every buyer”.

# Sample #4: Calculating Transferring Averages & Cumulative Sums

 
The transferring (rolling) common calculates the common during the last N rows, usually months or days. It’s calculated utilizing the AVG() window perform and defining the window as ROWS BETWEEN N PRECEDING AND CURRENT ROW.

The cumulative sum (operating complete) is the sum from the primary row as much as the present row, which is mirrored in defining the window as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW within the SUM() window perform.

Instance: The interview query from Amazon desires us to search out the 3-month rolling common of complete income from purchases. We should always output the year-month (YYYY-MM) and the 3-month rolling common, sorted from the earliest to the most recent month.

Additionally, the returns (detrimental buy values) shouldn’t be included.

We use a subquery to calculate month-to-month income utilizing SUM() and convert the acquisition date to a YYYY-MM format with the TO_CHAR() perform.

Then, we use AVG() to calculate the transferring common. Within the OVER() clause, we order the info in partition by month and outline the window as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW; we calculate the 3-month transferring common, which takes into consideration the present and the earlier two months.

SELECT t.month,
       AVG(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_revenue
FROM
  (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
          SUM(purchase_amt) AS monthly_revenue
   FROM amazon_purchases
   WHERE purchase_amt > 0
   GROUP BY 1
   ORDER BY 1) AS t
ORDER BY t.month ASC;

Right here’s the output.
 

To calculate a cumulative sum, we’d do it like this.

SELECT t.month,
       SUM(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum
FROM
  (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
          SUM(purchase_amt) AS monthly_revenue
   FROM amazon_purchases
   WHERE purchase_amt > 0
   GROUP BY 1
   ORDER BY 1) AS t
ORDER BY t.month ASC;

Right here’s the output.
 

Enterprise Use:

• Engagement metrics: 7-day transferring common of DAU or messages despatched, cumulative cancellations.
• Monetary KPIs: 30-day transferring common of prices/conversions/inventory costs, income reporting (cumulative YTD).
• Product efficiency: logins per consumer transferring common, cumulative app installs.
• Operations: cumulative orders shipped, tickets resolved, bugs closed.

# Sample #5: Making use of Conditional Aggregations

 
Conditional aggregation helps you to compute a number of segmented metrics in a single go by placing the CASE WHEN assertion inside mixture capabilities.

Instance: A query from an Amazon interview asks you to determine returning lively customers by discovering customers who made a second buy inside 1 to 7 days after their first buy. The output ought to consist solely of those customers’ IDs. The identical-day purchases must be ignored.

The primary CTE identifies the customers and the dates of their purchases, excluding same-day purchases through the use of the DISTINCT key phrase.

The second CTE ranks every consumer’s buy dates from the oldest to the latest.

The final CTE finds the primary and second purchases for every consumer through the use of conditional aggregation. We use MAX() to choose the only non-NULL worth for the primary and second buy dates.

Lastly, we use the results of the final CTE and retain solely customers who made a second buy (non-NULL) inside 7 days of their first buy.

WITH each day AS (
  SELECT DISTINCT user_id,
         created_at::DATE AS purchase_date
  FROM amazon_transactions
),

ranked AS (
  SELECT user_id,
         purchase_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn
  FROM each day
),

first_two AS (
  SELECT user_id,
         MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,
         MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date
  FROM ranked
  WHERE rn <= 2
  GROUP BY user_id
)

SELECT user_id
FROM first_two
WHERE second_date IS NOT NULL AND (second_date - first_date) BETWEEN 1 AND 7
ORDER BY user_id;

Right here’s the output.
 

Enterprise Use:

• Subscription reporting: paid vs. free customers, lively vs. churned customers by plan tier.
• Advertising funnel dashboards: signed up vs. bought customers by visitors supply, emails opened vs. clicked vs. transformed.
• E-commerce: accomplished vs. refunded vs. cancelled orders by area, new vs. returning consumers.
• Product evaluation: iOS vs. Android vs. Internet utilization, characteristic adopted vs. not adopted counts per cohort.
• Finance: income from new vs. current prospects, gross vs. internet income.
• A/B testing & experiments: management vs. remedy metrics.

# Conclusion

 
If you’d like a job at FAANG (and others, too) corporations, concentrate on these 5 SQL patterns for the interviews. After all, they’re not the one SQL ideas examined. However they’re mostly examined. By specializing in them, you make sure that your interview preparation is as environment friendly as attainable for many SQL interviews at FAANG corporations.
 
 

Nate Rosidi is a knowledge scientist and in product technique. He is additionally an adjunct professor educating analytics, and is the founding father of StrataScratch, a platform serving to knowledge scientists put together for his or her interviews with actual interview questions from prime corporations. Nate writes on the most recent traits within the profession market, offers interview recommendation, shares knowledge science tasks, and covers all the things SQL.