Customer Order Frequency | Advanced SQL Interview Questions | Data Engineer Interview Question

Video 333: This is the 23rd video of the SQL Interview Question series.

00:00 - Introduction to dataset and Question
07:20 - Approach 1: One-Step Aggregate Method
19:20 - Approach 2: Common Table Expression (CTE) Method
22:30 - Approach 3: Window Function Method
26:00 - Conclusion

We are given three tables Customers, Product and Orders

1. Customers table is the dimension table for customers which stores the customer's name and country of a customer
2. Product table is the dimension table for Products which stores the product description and price of the product
3. Orders table has the orders detail with order_id as the unique identifier, who is the customer who bought the product,
the product id, the date of order and the quantity.

To get the price of the purchase, we have to get the quantity, go to the Product table and get the Price, multiply it with the quantity to get the Sale price

What they are asking us to do is, find the customers who have spent at least $100 in each month of June and July 2024

In this video, we explore different SQL approaches to identify customers who have spent at least $100 in each month of June and July 2020. We will cover three distinct methods to achieve this goal using SQL queries.

*** Approach 1: One-Step Aggregate Method ***
This approach uses a single query with aggregate functions and CASE statements to filter and calculate the spend for each customer in June and July 2024. The result identifies customers who meet the spending criteria for both months.

*** Approach 2: Common Table Expression (CTE) Method ***
This approach leverages a CTE to first calculate the monthly spend for each customer. Then, it filters the results to identify customers who have spent at least $100 in both June and July 2024 by grouping the data and checking the spending condition.

*** Approach 3: Window Function Method ***
In this method, we use window functions to calculate the spend for each customer and month, and count the occurrences where the spend exceeds $100. The final selection filters customers who meet the spending requirement for both June and July 2024 using the window function results.

For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video.

Follow me on,

#sql #dataengineers #tablejoins #ceil #floor #bucket #meta #google #facebook #apple #paypal #netflix #amazon #deinterview #sqlinterview #interviewquestions #leetcode #faang #maanga #mysql #oracle #dbms #query #sqlserver #mysql #coderpad #aggregates #aggregation #nonaggregation #database #placementpreparation #lead #lag #windowsfunction #nullcheck #coalesce #sqlperformance #ifnull #case #lead #lag #windowsfunction #tamil #tamilpython #tamilinterview #tamilinterviewlatest #tamilinterviewquestions #sqlintamil