SQL | Data Analytics

Netflix Analytics Engineer Interview Question (SQL) 🚀 --- ### Scenario Overview Netflix wants to analyze user engagement with their platform. Imagine you have a table called netflix_data with the following columns: - user_id: Unique identifier for each user - subscription_plan: Type of subscription (e.g., Basic, Standard, Premium) - genre: Genre of the content the user watched (e.g., Drama, Comedy, Action) - timestamp: Date and time when the user watched a show - watch_duration: Length of time (in minutes) a user spent watching - country: User’s country The main objective is to figure out how to get insights into user behavior, such as which genres are most popular or how watch duration varies across subscription plans. --- ### Typical Interview Question > “Using the `netflix_data` table, find the top 3 genres by average watch duration in each subscription plan, and return both the genre and the average watch duration.” This question tests your ability to: 1. Filter or group data by subscription plan. 2. Calculate average watch duration within each group. 3. Sort results to find the “top 3” within each group. 4. Handle tie situations or edge cases (e.g., if there are fewer than 3 genres). --- ### Step-by-Step Approach 1. Group and Aggregate Use the GROUP BY clause to group by subscription_plan and genre. Then, use an aggregate function like AVG(watch_duration) to get the average watch time for each combination. 2. Rank Genres You can utilize a window function—commonly ROW_NUMBER() or `RANK()`—to assign a ranking to each genre within its subscription plan, based on the average watch duration. For example:

   AVG(watch_duration) OVER (PARTITION BY subscription_plan ORDER BY AVG(watch_duration) DESC)
   

(Note that in many SQL dialects, you’ll need a subquery because you can’t directly apply an aggregate in the ORDER BY of a window function.) 3. Select Top 3 After ranking rows in each partition (i.e., subscription plan), pick only the top 3 by watch duration. This could look like:

   SELECT subscription_plan,
          genre,
          avg_watch_duration
   FROM (
       SELECT subscription_plan,
              genre,
              AVG(watch_duration) AS avg_watch_duration,
              ROW_NUMBER() OVER (
                  PARTITION BY subscription_plan 
                  ORDER BY AVG(watch_duration) DESC
              ) AS rn
       FROM netflix_data
       GROUP BY subscription_plan, genre
   ) ranked
   WHERE rn <= 3;
   

4. Validate Results - Make sure each subscription plan returns up to 3 genres. - Check for potential ties. Depending on the question, you might use RANK() or DENSE_RANK() to handle ties differently. - Confirm the data type and units for watch_duration (minutes, seconds, etc.). --- ### Key Takeaways - Window Functions: Essential for ranking or partitioning data. - Aggregations & Grouping: A foundational concept for Analytics Engineers. - Data Validation: Always confirm you’re interpreting columns (like `watch_duration`) correctly. By mastering these techniques, you’ll be better prepared for SQL interview questions that delve into real-world scenarios—especially at a data-driven company like Netflix. --- *Stay tuned for more #SQL tips and interview insights!*

SQL Tricks to Level Up Your Database Skills 🚀 SQL is a powerful language, but mastering a few clever tricks can make your queries faster, cleaner, and more efficient. Here are some cool SQL hacks to boost your skills: 1️⃣ Use COALESCE Instead of CASE Instead of writing a long CASE statement to handle NULL values, use COALESCE():

SELECT COALESCE(name, 'Unknown') FROM users;

This returns the first non-null value in the list. 2️⃣ Generate Sequential Numbers Without a Table Need a sequence of numbers but don’t have a numbers table? Use GENERATE_SERIES (PostgreSQL) or WITH RECURSIVE (MySQL 8+):

SELECT generate_series(1, 10);

3️⃣ Find Duplicates Quickly Easily identify duplicate values with GROUP BY and HAVING:

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

4️⃣ Randomly Select Rows Want a random sample of data? Use: - PostgreSQL: ORDER BY RANDOM() - MySQL: ORDER BY RAND() - SQL Server: ORDER BY NEWID() 5️⃣ Pivot Data Without PIVOT (For Databases Without It) Use CASE with SUM() to pivot data manually:

SELECT 
    user_id,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM users
GROUP BY user_id;

6️⃣ Efficiently Get the Last Inserted ID Instead of running a separate SELECT, use: - MySQL: SELECT LAST_INSERT_ID(); - PostgreSQL: RETURNING id; - SQL Server: SELECT SCOPE_IDENTITY(); SQL is full of hidden gems—what are your favorite tricks? Let’s discuss in the comments! 💬🔍 #SQL #Database

SQL Noir is another educational project that makes learning SQL more fun. Instead of tutorials, you are in the role of a detective and investigate crimes by solving #SQL problems.

Advanced SQL Optimization Tips for Data Analysts 1. Use Proper Indexing Create indexes on frequently queried columns to speed up data retrieval. 2. Avoid `SELECT *` Specify only the columns you need to reduce the amount of data processed. 3. Use `WHERE` Instead of `HAVING` Filter your data as early as possible in the query to optimize performance. 4. Limit Joins Try to keep joins to a minimum to reduce query complexity and processing time. 5. Apply `LIMIT` or `TOP` Retrieve only the required rows to save on resources. 6. Optimize Joins Use INNER JOIN instead of OUTER JOIN whenever possible. 7. Use Temporary Tables Break large, complex queries into smaller parts using temporary tables. 8. Avoid Functions on Indexed Columns Using functions on indexed columns often prevents the index from being used. 9. Use CTEs for Readability Common Table Expressions help simplify nested queries and improve clarity. 10. Analyze Execution Plans Leverage execution plans to identify bottlenecks and make targeted optimizations. Happy querying!

Refactoring Analytics Models Sooner or later, every data professional faces the need to refactor analytical models. For example, you might need to migrate SQL models from Airflow to dbt, or overhaul a dbt project for scalability and best practices. While every refactoring project is unique, there are some common rules that can help guide the process. Here are 6 guidelines to help you succeed. 🔹 Rule 1. Don't break production analytics Make sure that production reports and systems are not affected. Refactoring is the full responsibility of engineers and analysts. 🔹 Rule 2. Define new rules Define conventions that satisfy your team, like modeling layers, naming rules, etc. 🔹 Rule 3. Inspect existing models It helps in creating a refactoring plan: - Some models may be easy to migrate (loosely coupled) - Some models will require additional work 🔹 Rule 4. Start from the end Knowing the end goal will help you decide which data sources and intermediates are required for the final table. 🔹 Rule 5. Proceed in small chunks Proceed in small increments. Deliver small, yet complete, changes that are easy to review and deploy. 🔹 Rule 6. Beginning is hard, but it gets easier At the start of the migration, you will face a lot of work: every new model will be implemented from scratch and require significant groundwork. However, the more you do at the beginning, the easier it will be in the end. #sql #dbt #dataanalytics

10 Optimisation Tips to Reduce Your Mighty BigQuery Cost https://bit.ly/3DNGJP1 #SQL #bigquery

Key in #sql

The dbt Data Modeling Challenge - Fantasy Football Edition is LIVE! 🔥 paradime.io, Lightdash and I have teamed up to bring you a hackathon that combines data modeling with America's favorite sport. Ready to transform raw fantasy football data into game-winning insights? Here's your playbook for success: - Build powerful dbt™ models using real NFL & fantasy football data - Leverage industry-leading tools: Paradime, Snowflake, and Lightdash - Submit before the Super Bowl for a chance to win big Prize Pool: 🥇 $1,500 Amazon Gift Card 🥈 $1,000 Amazon Gift Card 🥉 $500 Amazon Gift Card Key Dates: 🏃‍♂️ Kickoff: January 2, 2025 🏁 Final Whistle: February 4, 2025 (11:59 PM PT) 🏆 Winners Announced: February 6, 2025 (Just in time for the Super Bowl!) Whether you're a seasoned data professional or an up-and-comer, this is your chance to demonstrate & improve your skills, enhance your project portfolio, compete for the big prizes. Ready to join? Register now - https://www.paradime.io/dbt-data-modeling-challenge #dbt #sql #dataanalyst #paradime #football

#DataAnalyst #python

#SQL JOINS Cheat Sheet

🖥 Roadmap to become Data Analyst

🌟 Data Analyst vs Business Analyst: Quick comparison 🌟 1. Data Analyst: Dives into data, cleans it up, and finds hidden insights like Sherlock Holmes. 🕵️‍♂️ Business Analyst: Talks to stakeholders, defines requirements, and ensures everyone’s on the same page. The diplomat. 🤝 2. Data Analyst: Master of Excel, SQL, Python, and dashboards. Their life is rows, columns, and code. 📊 Business Analyst: Fluent in meetings, presentations, and documentation. Their life is all about people and processes. 🗂️ 3. Data Analyst: Focuses on numbers, patterns, and trends to tell a story with data. 📈 Business Analyst: Focuses on the "why" behind the numbers to help the business make decisions. 💡 4. Data Analyst: Creates beautiful Power BI or Tableau dashboards that wow stakeholders. 🎨 Business Analyst: Uses those dashboards to present actionable insights to the C-suite. 🎤 5. Data Analyst: SQL queries, Python scripts, and statistical models are their weapons. 🛠️ Business Analyst: Process diagrams, requirement docs, and communication are their superpowers. 🦸‍♂️ 6. Data Analyst: “Why is revenue declining? Let me analyze the sales data.” Business Analyst: “Why is revenue declining? Let’s talk to the sales team and fix the process.” Data Analysts are the ones diving deep into the numbers and providing the evidence. Business Analysts are the ones shaping those insights into actionable plans that steer the business forward. Both roles are vital, but they approach the data world in their unique ways. #DataAnalyst #SQL #Analyst

DBT best practices in action at Cal-ITP’s data-infra project. dbt adoption is growing, projects are getting bigger, and more fingers are now in your data pie than ever. Your job of maintaining data quality and prod stability in such circumstances isn’t easy, which is bringing the topic of dbt/data project best practices to the forefront. https://medium.com/inthepipeline/dbt-best-practices-in-action-at-cal-itps-data-infra-project-0d11adf5513d #dbt #sql

Merry Christmas 🔔🦌🍪🎄🎏

🔥 Recent Data Analyst Interview Q&A at Deloitte 🔥 Question: 👉 Write an SQL query to extract the third highest salary from an employee table with columns EID and ESalary. Solution:

SELECT ESalary  
FROM (  
  SELECT ESalary,  
         DENSE_RANK() OVER (ORDER BY ESalary DESC) AS salary_rank  
  FROM employee  
) AS ranked_salaries  
WHERE salary_rank = 3;

Explanation of the Query: 1️⃣ Step 1: Create a Subquery The subquery ranks all salaries in descending order using DENSE_RANK(). 2️⃣ Step 2: Rank the Salaries Assigns ranks: 1 for the highest salary, 2 for the second-highest, and so on. 3️⃣ Step 3: Assign an Alias The subquery is given an alias (ranked_salaries) to use in the main query. 4️⃣ Step 4: Filter for the Third Highest Salary The WHERE clause filters the results to include only the salary with rank 3. 5️⃣ Step 5: Display the Third Highest Salary The main query selects and displays the third-highest salary. By following these steps, you can easily extract the third-highest salary from the table. #DataAnalyst #SQL #InterviewTips

Data Engineering Zoomcamp - 2025 Cohort Start: 13 January 2025 Registration link: https://airtable.com/shr6oVXeQvSI5HuWD #sql #dataanalyst

Top 10 Advanced SQL Interview Questions and Answers 1. What is a Common Table Expression (CTE), and when would you use it? A Common Table Expression (CTE) is a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement. Example:

   WITH SalesCTE AS (
       SELECT SalespersonID, SUM(SalesAmount) AS TotalSales
       FROM Sales
       GROUP BY SalespersonID
   )
   SELECT * FROM SalesCTE WHERE TotalSales > 5000;
   

2. How do you optimize a query with a large dataset? - Use proper indexes. - Avoid SELECT *; only retrieve required columns. - Break down complex queries using temporary tables or CTEs. - Analyze query execution plans. 3. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()? - RANK(): Skips ranking if there’s a tie (e.g., 1, 2, 2, 4). - DENSE_RANK(): Does not skip ranks after a tie (e.g., 1, 2, 2, 3). - ROW_NUMBER(): Assigns unique numbers sequentially, regardless of ties. 4. How do you find duplicate records in a table?

   SELECT ColumnName, COUNT(*)
   FROM TableName
   GROUP BY ColumnName
   HAVING COUNT(*) > 1;
   

5. What is the difference between INNER JOIN and LEFT JOIN? - INNER JOIN: Returns records that match in both tables. - LEFT JOIN: Returns all records from the left table, and matching records from the right table (NULL if no match). 6. Explain window functions and provide an example. Window functions operate on a set of rows related to the current row, without collapsing them into a single output. Example:

   SELECT EmployeeID, Salary, 
          RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
   FROM Employees;
   

7. What are the different types of indexes in SQL? - Clustered Index: Reorders the data physically in the table. - Non-Clustered Index: Creates a separate structure for data retrieval. - Unique Index: Ensures no duplicate values in the column. 8. How do you handle NULL values in SQL? - Use COALESCE() or ISNULL() to replace NULL values. - Filter with IS NULL or IS NOT NULL in WHERE clauses. Example:

   SELECT COALESCE(PhoneNumber, 'N/A') AS ContactNumber FROM Customers;
   

9. What is the difference between DELETE and TRUNCATE? - DELETE: Removes specific rows, can use WHERE clause, and logs individual row deletions. - TRUNCATE: Removes all rows, faster, and resets table identity. 10. How do you use a CASE statement in SQL?

    SELECT ProductName,
           CASE 
               WHEN Quantity > 100 THEN 'High Stock'
               WHEN Quantity BETWEEN 50 AND 100 THEN 'Medium Stock'
               ELSE 'Low Stock'
           END AS StockStatus
    FROM Products;
    

Perfect for preparing for advanced #SQL interviews or brushing up your skills! 💡

It's true 😁