Top 45 MySQL Interview Questions You Must Know In 2024 [Basic to Advanced]

MySQL ranks in the top three most popular database management systems worldwide, alongside Microsoft SQL and Oracle, with Oracle being number one. The reason for the widespread use of MySQL is its reliability, ease of use, and responsiveness. It’s often the go-to choice for web development, but it also supports large-scale operations. Even after being acquired by Oracle Corporation, it remains open-source.

Although MySQL is pretty straightforward and beginner-friendly, some advanced features might leave you unprepared for your next data-related interview.

So, if you’re looking to land a job in data science or analytics, this is the perfect guide for you! We’ve crafted a list of the top 45 MySQL interview questions for data analysts.

Given the widespread use of MySQL by organizations like GitHub, Airbnb, and Uber for their data-related solutions, you’ll likely encounter questions about MySQL during data and tech interview rounds.

As a candidate in the data science and analytics field, you’ve come to the right place to find SQL and MySQL-based questions from beginner to advanced. We’ve compiled a list of 45 MySQL interview questions and answers we believe you’ll find intriguing.

Let’s look at some relatively easy MySQL Interview Questions to get you in the groove. However, if you first want to brush up on your SQL, you can check out our SQL learning path.

1. What is the difference between a DBMS and an RDBMS?

“A DBMS (database management system) is software that manages databases. An RDBMS (relational database management system) is a type of DBMS that uses a relational model to organize data into tables with rows and columns and supports SQL for database access.”

2. Explain the ACID properties of a transaction in a database.

“ACID stands for atomicity, consistency, isolation, and durability. Atomicity ensures all operations within a transaction are completed; if not, the transaction is aborted. Consistency ensures the database remains in a valid state before and after the transaction. Isolation ensures transactions are securely and independently processed without interference. Durability ensures that once a transaction is committed, it remains so, even in the event of a system failure.”

3. Describe the difference between a primary key and a unique constraint.

“A primary key uniquely identifies each record in a table and cannot be NULL, ensuring entity integrity. A unique constraint also ensures all values in a column or a set of columns are distinct across rows, but unlike the primary key, it allows one NULL value.”

4. You’re getting a “Lost connection to MySQL server” error. What steps would you take to diagnose the issue?

“I would start by checking the MySQL server status to ensure it’s running. Then, I would examine the MySQL error logs for any clues. Next, I would verify the network connectivity and firewall settings between the client and server. I would then review the MySQL configuration parameters such as max_allowed_packet and wait_timeout to ensure they are set appropriately.”

5. What are the different types of SQL joins (e.g., INNER JOIN, LEFT JOIN, etc.)? Briefly explain their functionalities.

“INNER JOIN returns rows when there is a match in both tables. LEFT JOIN returns all rows from the left table and matched rows from the right table; if no match, NULLs are returned for columns from the right table. RIGHT JOIN is the opposite of LEFT JOIN, returning all rows from the right table and matched rows from the left table. FULL OUTER JOIN returns rows when there is a match in one of the tables, returning NULLs for non-matching rows from either table.”

6. How would you optimize a slow SELECT query in MySQL? Explain your approach.

“To optimize a slow MySQL query, I’d first analyze the query itself and its execution plan using EXPLAIN. This helps identify bottlenecks like missing indexes or inefficient WHERE clauses. Then, I can optimize with targeted actions like creating indexes or refining the query to leverage existing ones. Finally, I’d test and monitor to ensure the optimization holds under the real workload.”

7. Explain the benefits of using indexes in MySQL and how they improve performance.

“Indexes significantly enhance query performance by reducing the amount of data the database engine needs to scan to find the results. They allow faster data retrieval using binary search or other efficient lookup algorithms. Proper indexing can lead to reduced I/O operations, lower CPU usage, and faster query execution times.”

8. How can you secure a MySQL database against unauthorized access? Discuss different methods.

“Securing a MySQL database involves several measures:

1. Setting strong passwords for all MySQL users.
2. Configuring the MySQL server to listen only on localhost if remote access isn’t required.
3. Using SSL/TLS for encrypted connections.
4. Implementing firewall rules to restrict access.
5. Regularly updating MySQL to patch security vulnerabilities.
6. Granting minimal privileges necessary for each user using the principle of least privilege.
7. Enabling MySQL’s built-in security features like the mysql_secure_installation script.”

9. Differentiate between the SELECT * and specifying column names in a SELECT statement.

“Using SELECT retrieves all columns from a table, which can lead to inefficiencies if only a subset of columns is needed. Specifying column names in a SELECT statement retrieves only the required data, improving performance and reducing network load.”

10. Explain the concept of a subquery in SQL.

“A subquery, also known as an inner query or nested query, is a query within another SQL query. It is used to perform operations in multiple steps, often providing a result set that the outer query can use for filtering, comparison, or aggregation.”

11. Describe stored procedures in MySQL and their advantages over regular SQL statements.

“Stored procedures in MySQL are precompiled SQL codes stored in the database, which can be executed with a single call. Advantages include improved performance through precompilation, reduced network traffic by executing multiple statements in one call, enhanced security by encapsulating business logic, and maintainability through modularization of repetitive tasks.”

12. What are some security best practices to prevent SQL injection attacks?

“To prevent SQL injection attacks, employ parameterized queries or prepared statements to separate SQL code from user input. Validate and sanitize input data to ensure it conforms to expected formats. Use the least privilege principles by granting minimal database permissions to users. Regularly update and patch database software to mitigate known vulnerabilities. Implement web application firewalls (WAFs) and input validation techniques to filter out malicious inputs.”

13. Describe the functionalities of the WHERE and HAVING clauses in a SELECT statement.

“The WHERE clause filters rows based on specified criteria before the query retrieves data from the database. It restricts the result set to only those rows that meet the conditions specified. The HAVING clause, on the other hand, filters rows based on aggregated values, such as those returned by GROUP BY clauses. It applies conditions to groups created by the GROUP BY clause and filters the groups based on the specified criteria.”

14. What is the difference between DELETE and TRUNCATE statements?

“The DELETE statement removes rows from a table based on specified conditions, allowing for selective deletion. It also generates transaction logs and triggers, providing the possibility of rolling back changes. The TRUNCATE statement removes all rows from a table, resetting auto-incremented columns and deallocating storage space. Unlike DELETE, TRUNCATE is not transactional and cannot be rolled back. Additionally, it resets identity columns to their seed value.”

15. What is MySQL replication, and how can it be used for disaster recovery?

“MySQL replication is a process where data from one MySQL server (master) is copied to one or more MySQL servers (slaves). It supports disaster recovery by providing real-time data redundancy, allowing failover to a replica if the master fails, and enabling load balancing for read operations across multiple servers.”

Now, we can move on to intermediate-level MySQL questions designed exclusively for data science domain candidates.

However, if you feel that running through MySQL questions won’t prepare you for those daunting data science interviews, check out our coaching sessions. This will give you some practice in your technical knowledge and that confidence boost we all need for interviews!

But if you’re more of a straightforward learning type, let’s get back to the questions for your intermediate MySQL interview preparation!

16. Let’s say you work at Uber. The rides table contains information about the trips of Uber users across America.

Write a query to get the average commute time (in minutes) for each commuter in New York (NY) and the average commute time (in minutes) across all commuters in New York.

Example:

Input:

rides table

Column	Type
id	INTEGER
commuter_id	INTEGER
start_dt	DATETIME
end_dt	DATETIME
city	VARCHAR

Output:

Column	Type
commuter_id	INTEGER
avg_commuter_time	FLOAT
avg_time	FLOAT

Display results like:

commuter_id	avg_commuter_time	avg_time
11	27	45
22	97	45
33	11	45

Answer:

SELECT
  a.commuter_id,
  a.avg_commuter_time,
  b.avg_time
FROM (
  SELECT
    commuter_id,
    city,
    FLOOR(AVG(TIMESTAMPDIFF(MINUTE, start_dt, end_dt))) AS avg_commuter_time
  FROM rides
  WHERE city = 'NY'
  GROUP BY commuter_id
) a
LEFT JOIN (
  SELECT
    FLOOR(AVG(TIMESTAMPDIFF(MINUTE, start_dt, end_dt))) AS avg_time,
    city
  FROM rides
  WHERE city = 'NY'
) b
ON a.city = b.city

17. Suppose we have a table of transactions that happened during 2023, with each transaction belonging to different departments within a company. We want to calculate the total spend for IT, HR, and Marketing and also have a total for Other departments, grouped by fiscal quarters.

Write a query to display this result.

Note: Display only quarters where at least one transaction occurred. Quarter names should be Q1, Q2, Q3 and Q4. Q1 is from January to March.

Example:

Input:

transactions table

Column	Type
transaction_id	INTEGER
department	VARCHAR
amount	FLOAT
transaction_date	DATE

Output:

Column	Type
quarter	VARCHAR
it_spending	FLOAT
hr_spending	FLOAT
marketing_spending	FLOAT
other_spending	FLOAT

Answer:

SELECT
  CASE
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 1 AND 3 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 4 AND 6 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 7 AND 9 THEN 'Q3'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 10 AND 12 THEN 'Q4'
  END AS quarter,
  SUM(CASE WHEN department = 'IT' THEN amount ELSE 0 END) AS it_spending,
  SUM(CASE WHEN department = 'HR' THEN amount ELSE 0 END) AS hr_spending,
  SUM(CASE WHEN department = 'Marketing' THEN amount ELSE 0 END) AS marketing_spending,
  SUM(CASE WHEN department NOT IN ('IT', 'HR', 'Marketing') THEN amount ELSE 0 END) AS other_spending
FROM
  transactions
GROUP BY
  Quarter;

18. The HR department in your organization wants to calculate employees’ earnings. Write a query to report the sum of regular salaries, overtime pay, and total compensations for each role.

Example:

Input:

employees table

Column	Type
employee_id	INTEGER
job_title	VARCHAR
salary	FLOAT
overtime_hours	INTEGER
overtime_rate	FLOAT

Output:

Column	Type
job_title	VARCHAR
total_salaries	FLOAT
total_overtime_payments	FLOAT
total_compensation	FLOAT

Answer:

SELECT
  job_title,
  SUM(salary) AS total_salaries,
  SUM(overtime_hours * overtime_rate) AS total_overtime_payments,
  SUM(salary + (overtime_hours * overtime_rate)) AS total_compensation
FROM
  employees
GROUP BY
  job_title;

19. Given a table called user_experiences, write a query to determine the percentage of users who held the title of “data analyst” immediately before holding the title “data scientist.” Immediate is defined as the user holding no other titles between the “data analyst” and “data scientist” roles.

Example:

Input:

user_experiences table

Column	Type
id	INTEGER
position_name	VARCHAR
start_date	DATETIME
end_date	DATETIME
user_id	INTEGER

Output:

Column	Type
percentage	FLOAT

Answer:

WITH added_previous_role AS (
  SELECT user_id, position_name,
  LAG (position_name)
  OVER (PARTITION BY user_id)
  AS previous_role
  FROM user_experiences
),

experienced_subset AS (
  SELECT *
  FROM added_previous_role
  WHERE position_name = 'Data Scientist'
    AND previous_role = 'Data Analyst'
)

SELECT COUNT(DISTINCT experienced_subset.user_id)/
     COUNT(DISTINCT user_experiences.user_id)
AS percentage
FROM user_experiences
LEFT JOIN experienced_subset
    ON user_experiences.user_id = experienced_subset.user_id

20. Given the transactions table below, write a query that finds the third purchase of every user.

Note: Sort the results by the user_id in ascending order. If a user purchases two products at the same time, the lower id field is used to determine which is the first purchase.

Example:

Input:

transactions table

Columns	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

Output:

Columns	Type
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

Answer:

SELECT user_id, created_at, product_id, quantity
FROM (
    SELECT
        user_id
        , created_at
        , product_id
        , quantity
        , RANK() OVER (
            PARTITION BY user_id
            ORDER BY created_at ASC, id ASC
        ) AS rank_value
    FROM transactions
) AS t
WHERE rank_value = 3
ORDER BY user_id ASC

21. Write a query to show the number of users, number of transactions placed, and total order amount per month in the year 2020. Assume that we are only interested in the monthly reports for a single year (January–December).

Example:

Input:

transactions table

Column	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

products table

Column	Type
id	INTEGER
name	VARCHAR
price	FLOAT

users table

Column	Type
id	INTEGER
name	VARCHAR
sex	VARCHAR

Output:

Column	Type
month	INTEGER
num_customers	INTEGER
num_orders	INTEGER
order_amt	INTEGER

Answer:

SELECT MONTH(t.created_at) AS month,
COUNT(DISTINCT t.user_id) AS num_customers,
COUNT(t.id) AS num_orders,
SUM(t.quantity * p.price) AS order_amt
FROM transactions t
JOIN products p
ON t.product_id = p.id
WHERE YEAR(created_at) ='2020'
GROUP BY 1

22. Given tables employees, employee_projects, and projects, find the 3 lowest-paid employees that have completed at least 2 projects.

Note: incomplete projects will have an end date of NULL in the projects table.

Example:

Input:

employees table

Column	Type
id	INTEGER
first_name	VARCHAR
last_name	VARCHAR
salary	INTEGER
department_id	INTEGER

employee_projects table

Column	Type
employee_id	INTEGER
project_id	INTEGER

projects table

Column	Type
id	INTEGER
title	VARCHAR
start_date	DATE
end_date	DATE
budget	INTEGER

Output:

Column	Type
employee_id	INTEGER
salary	INTEGER
completed_projects	INTEGER

Answer:

SELECT  ep.employee_id
, e.salary
, COUNT(p.id) AS completed_projects

FROM employee_projects AS ep

JOIN employees AS e
ON e.id = ep.employee_id

JOIN projects AS p
ON  ep.project_id = p.id

WHERE p.end_date IS NOT NULL
GROUP BY 1
HAVING completed_projects > 1
ORDER BY 2
LIMIT 3

23. How can ACLs be used to control access to specific database objects in MySQL?

“Access control lists (ACLs) in MySQL manage permissions for users by defining what actions they can perform on specific database objects. ACLs can grant or revoke privileges, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE, on databases, tables, columns, and stored procedures. They provide granular control, allowing different permissions for different users and roles.”

24. Explain the difference between character set and collation in MySQL and how they impact data storage and retrieval.

“A character set in MySQL defines the set of characters that can be stored in a database. Collation is a set of rules for comparing characters within a character set, dictating how text is sorted and compared. Character sets impact storage by determining the byte representation of characters, while collation impacts retrieval by defining how strings are ordered and compared, affecting operations like sorting and searching.”

25. Describe different types of locking mechanisms used by MySQL to ensure data consistency during concurrent access.

“MySQL employs various locking mechanisms such as table-level locks, row-level locks, and page-level locks to maintain data consistency during concurrent access. Table-level locks lock the entire table, row-level locks lock specific rows, and page-level locks lock groups of rows within a table to prevent conflicting modifications by concurrent transactions.”

26. How would you write a MySQL query that checks for errors and returns user-friendly messages instead of technical error codes?

“To write a MySQL query that checks for errors and returns user-friendly messages, I would utilize the MySQL error handling mechanism, specifically the TRY…CATCH block in stored procedures. Within the TRY block, I’d execute the query, and the CATCH block would handle any errors, providing customized error messages to the user.”

27. What are some strategies for optimizing performance when querying large tables in MySQL?

“Strategies for optimizing performance when querying large tables in MySQL include indexing frequently queried columns, partitioning the table based on usage patterns, optimizing queries by using appropriate joins and WHERE clauses, denormalizing data to reduce join complexity, and utilizing caching mechanisms such as query caching and Memcached for frequently accessed data.”

28. Explain how self-joins can be used in MySQL to identify relationships within a single table. Provide an example scenario where a self-join might be useful.

“Self-joins in MySQL are used to establish relationships within a single table by joining it with itself. An example scenario where a self-join might be useful is in a hierarchical structure such as an organizational chart. For instance, to retrieve the names of all employees along with their managers’ names, a self-join on the employee table can be performed based on the manager’s ID.”

29. Explain the concept of federated tables in MySQL and their limitations compared to traditional tables.

“Federated tables in MySQL allow access to data from remote databases as if they are local tables. However, they come with limitations such as lack of support for transactions, limited functionality compared to native storage engines, and potential performance overhead due to network latency and data transfer.”

30. You are given a slow UPDATE query. Describe different techniques you might use to analyze and optimize the query performance in MySQL.

“To optimize the performance of a slow UPDATE query in MySQL, I would begin by analyzing the query execution plan using EXPLAIN to identify potential bottlenecks. Then, I might consider optimizing indexes, rewriting the query to minimize the number of rows updated, breaking down the query into smaller transactions, using temporary tables for complex calculations, or optimizing disk I/O by tuning buffer pool sizes and disk configuration.”

If you are unsure if you want to read through all these questions and answers before we continue into the advanced MySQL interview questions, we recommend trying our challenges. They’re 15- to 30-minute challenges to test your knowledge in your respective field.

Now, let’s get back to the questions. Note that this section is strictly for interviews at companies that use MySQL to manage their databases. Here are a few recurring MySQL questions that are asked in senior data science role interviews:

31. The schema below is for a retail online shopping company consisting of two tables, attribution and user_sessions.

• The attribution table logs a session visit for each row.
• If conversion is true, then the user converted to buying on that session.
• The channel column represents which advertising platform the user was attributed to for that specific session.
• Lastly, the user_sessions table maps many one-session visits back to one user.

First touch attribution is defined as the channel with which the converted user was associated when they first discovered the website.

Calculate the first touch attribution for each user_id that is converted.

Example:

Input:

attribution table

Column	Type
session_id	INTEGER
channel	VARCHAR
conversion	BOOLEAN

user_sessions table

column	type
session_id	INTEGER
created_at	DATETIME
user_id	INTEGER

Example output:

user_id	channel
123	facebook
145	google
153	facebook
172	organic
173	email

Answer:

WITH conv AS (
    SELECT us.user_id
    FROM attribution AS a
    INNER JOIN user_sessions AS us
        ON a.session_id = us.session_id
    WHERE conversion = 1
    GROUP BY 1 -- group by to get distinct user_ids
),

-- get the first session by user_id and created_at time.
first_session AS (
    SELECT
        min(us.created_at) AS min_created_at
        , conv.user_id
    FROM user_sessions AS us
    INNER JOIN conv
        ON us.user_id = conv.user_id
    INNER JOIN attribution AS a
        ON a.session_id = us.session_id
    GROUP BY conv.user_id
)

-- join user_id and created_at time back to the original table.
SELECT us.user_id, channel
FROM attribution
JOIN user_sessions AS us
    ON attribution.session_id = us.session_id
-- now join the first session to get a single row for each user_id
JOIN first_session
-- double join
    ON first_session.min_created_at = us.created_at
        AND first_session.user_id = us.user_id

32. There are two tables. One table is called swipes that holds a row for every Tinder swipe and contains a Boolean column that determines if the swipe was a right or left swipe called is_right_swipe. The second is a table named variants that determine which user has which variant of an AB test.

Write an SQL query to output the average number of right swipes for two different variants of a feed ranking algorithm by comparing users that have swiped 10, 50, and 100 swipes in a feed_change experiment.

Note: Users have to have swiped at least 10 times to be included in the subset of users to analyze the mean number of right swipes.

Example:

Input:

variants table

Columns	Type
id	INTEGER
experiment	VARCHAR
variant	VARCHAR
user_id	INTEGER

swipes table

Columns	Type
id	INTEGER
user_id	INTEGER
swiped_user_id	INTEGER
created_at	DATETIME
is_right_swipe	BOOLEAN

Output:

Columns	Type
variant	VARCHAR
mean_right_swipes	FLOAT
swipe_threshold	INTEGER
num_users	INTEGER

Answer:

WITH swipe_ranks AS (
    SELECT
        swipes.user_id
        , variant
        , RANK() OVER (
            PARTITION BY user_id ORDER BY created_at ASC
        ) AS ranks
        , is_right_swipe
    FROM swipes
    INNER JOIN variants
        ON swipes.user_id = variants.user_id
    WHERE experiment = 'feed_change'
)

SELECT
    variant
    , CAST(SUM(is_right_swipe) AS DECIMAL)/COUNT(DISTINCT sr.user_id) AS mean_right_swipes
    , 10 AS swipe_threshold
    , COUNT(DISTINCT sr.user_id) AS num_users
FROM swipe_ranks AS sr
INNER JOIN (
    SELECT user_id
    FROM swipe_ranks
    WHERE ranks >= 10
    GROUP BY 1
) AS subset
    ON subset.user_id = sr.user_id
WHERE ranks <= 10
GROUP BY 1

UNION ALL

SELECT
    variant
    , CAST(SUM(is_right_swipe) AS DECIMAL)/COUNT(DISTINCT sr.user_id) AS mean_right_swipes
    , 50 AS swipe_threshold
    , COUNT(DISTINCT sr.user_id) AS num_users
FROM swipe_ranks AS sr
INNER JOIN (
    SELECT user_id
    FROM swipe_ranks
    WHERE ranks >= 50
    GROUP BY 1
) AS subset
    ON subset.user_id = sr.user_id
WHERE ranks <= 50
GROUP BY 1

UNION ALL

SELECT
    variant
    , CAST(SUM(is_right_swipe) AS DECIMAL)/COUNT(DISTINCT sr.user_id) AS mean_right_swipes
    , 100 AS swipe_threshold
    , COUNT(DISTINCT sr.user_id) AS num_users
FROM swipe_ranks AS sr
INNER JOIN (
    SELECT user_id
    FROM swipe_ranks
    WHERE ranks >= 100
    GROUP BY 1
) AS subset
    ON subset.user_id = sr.user_id
WHERE ranks <= 100
GROUP BY 1

33. You’re given three tables: users, transactions, and events. We’re interested in how user activity affects user purchasing behavior. The events table holds data for user events on the website, where the action field would equal values such as like and comment.

Write a query to prove if users who interact on the website (likes, comments) convert toward purchasing at a higher volume than users who do not interact.

users table

column	type
id	INTEGER
name	VARCHAR
created_at	DATETIME

transactions table

column	type
user_id	INTEGER
name	VARCHAR
created_at	DATETIME

events table

column	type
user_id	INTEGER
action	VARCHAR
created_at	DATETIME

Answer:

SELECT
  AVG(t.num_transactions) AS avg_transactions_with_interactions,
  AVG(t2.num_transactions) AS avg_transactions_without_interactions
FROM (
  SELECT
    e.user_id,
    COUNT(DISTINCT t.id) AS num_transactions
  FROM events e
  INNER JOIN transactions t
      ON e.user_id = t.user_id
  WHERE e.action IN ('like', 'comment')
  GROUP BY e.user_id
) t
INNER JOIN (
  SELECT
    u.id,
    COUNT(DISTINCT t.id) AS num_transactions
  FROM users u
  LEFT JOIN events e
      ON u.id = e.user_id
          AND e.action IN ('like', 'comment')
  INNER JOIN transactions t
      ON u.id = t.user_id
  WHERE e.user_id IS NULL
  GROUP BY u.id
) t2
ON t.user_id = t2.id

34. Given a table, how many customers who signed up in January 2020 had a combined (successful) sending and receiving volume greater than $100 in their first 30 days?

Note: The sender_id and recipient_id both represent the user_id.

payments table

Column	Type
payment_id	INTEGER
sender_id	INTEGER
recipient_id	INTEGER
created_at	DATETIME
payment_state	VARCHAR
amount_cents	INTEGER

users table

Column	Type
id	INTEGER
created_at	DATETIME

Output:

Column	Type
num_customers	INTEGER

Answer:

WITH cte_transform_payments AS
(
 SELECT sender_id AS ID,created_at as tran_date,payment_state, ABS(amount_cents) AS amount_cents, 1 AS s_r FROM payments
 UNION ALL
 SELECT recipient_id AS ID,created_at as tran_date,payment_state, ABS(amount_cents) AS amount_cents , 0 AS s_r FROM payments
),
cte_time_differences AS (
 SELECT a.*,b.created_at AS user_reg_date, DATEDIFF(tran_date,b.created_at) AS date_diff FROM cte_transform_payments a
 JOIN users b on a.ID = b.id
),
cte_data_filtering AS (
 SELECT * FROM cte_time_differences
 WHERE user_reg_date>='2020-01-01 00:00:00' AND user_reg_date<'2020-01-31 00:00:00'
  AND date_diff >= 0 AND date_diff <= 30 AND payment_state = 'success'
),
cte_grouping AS (
 SELECT id , SUM(amount_cents) FROM cte_data_filtering
 GROUP BY id
 HAVING SUM(amount_cents) > 10000
)
SELECT COUNT(*) AS num_customers FROM cte_grouping

35. Given the two tables, write an SQL query that creates a cumulative distribution of the number of comments per user. Assume bin buckets class intervals of one.

Example:

Input:

users table

Columns	Type
id	INTEGER
name	VARCHAR
created_at	DATETIME
neighborhood_id	INTEGER
sex	VARCHAR

comments table

Columns	Type
user_id	INTEGER
body	VARCHAR
created_at	DATETIME

Output:

Columns	Type
frequency	INTEGER
cum_total	FLOAT

Answer:

WITH hist AS (
    SELECT users.id, COUNT(c.user_id) AS frequency
    FROM users
    LEFT JOIN comments as c
        ON users.id = c.user_id
    GROUP BY 1
),

freq AS (
    SELECT frequency, COUNT(*) AS num_users
    FROM hist
    GROUP BY 1
)

SELECT f1.frequency, SUM(f2.num_users) AS cum_total
FROM freq AS f1
LEFT JOIN freq AS f2
    ON f1.frequency >= f2.frequency
GROUP BY 1

36. We’re given a table of user experiences representing each person’s past work experiences and timelines. Specifically, let’s say we’re interested in analyzing the career paths of data scientists. Let’s say that the titles we care about are bucketed into data scientist, senior data scientist, and data science manager.

We’re interested in determining if a data scientist who switches jobs more often gets promoted to a manager role faster than a data scientist who stays at one job longer.

Write a query to prove or disprove this hypothesis.

Example:

Input:

user_experiences table

Column	Type
id	INTEGER
user_id	INTEGER
title	VARCHAR
company	VARCHAR
start_date	DATETIME
end_date	DATETIME
is_current_role	BOOLEAN

Answer:

WITH manager_promo as (
    SELECT user_id
        , MIN(start_date) as promotion_date
    FROM playground.user_experiences
    WHERE title='data science manager'
    GROUP BY user_id
)

SELECT num_jobs_switched
    , AVG(
        TIMESTAMPDIFF(MONTH, career_started_date, promotion_date)
    ) as month_to_promo
FROM (
    SELECT u.user_id
        , mp.promotion_date
        , COUNT(DISTINCT u.id) as num_jobs_switched
        , MIN(start_date) as career_started_date
    FROM playground.user_experiences u
    INNER JOIN manager_promo mp
        on u.user_id=mp.user_id
    WHERE u.start_date = mp.promotion_date
    GROUP BY u.user_id, mp.promotion_date
) tt
GROUP BY num_jobs_switched
ORDER BY 2 DESC

37. A dating website’s schema is represented by a table of people who like other people. The table has three columns. One column is the user_id, another column is the liker_id which is the user_id of the user doing the liking, and the last column is the date time that the like occurred.

Write a query to count the number of liker’s likers (the users who like the likers) if the liker has any.

Example:

Input:

likes table