Skip to contentSkip to footer

Senior Product Analyst Interview Questions

Salaries
Interviews

519 senior product analyst interview questions shared by candidates

Here’s a set of advanced SQL questions with brief explanations of the solutions. These questions focus on analyzing data and applying logical thinking using SQL. --- ### Complex SQL Questions and Solutions with Brief Explanations #### **1. Consecutive Sessions Without Breaks** **Question:** You have a `user_sessions` table with columns `user_id`, `login_time`, and `logout_time`. Find users who had consecutive sessions with no breaks (i.e., `logout_time` of one session equals `login_time` of the next). **Solution Explanation:** Use a self-join on `user_sessions` to find records where `logout_time` matches the `login_time` of the next session for the same user. ```sql SELECT a.user_id, a.login_time AS first_login, a.logout_time AS first_logout, b.login_time AS second_login FROM user_sessions a JOIN user_sessions b ON a.user_id = b.user_id AND a.logout_time = b.login_time; ``` --- #### **2. Second Highest Salary in Each Department** **Question:** In an `employees` table with columns `employee_id`, `department_id`, and `salary`, find the second-highest salary in each department. **Solution Explanation:** Use `DENSE_RANK()` in a subquery to rank salaries within each department, then filter to keep only the second-highest rank. ```sql SELECT department_id, MAX(salary) AS second_highest_salary FROM ( SELECT department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees ) ranked_salaries WHERE salary_rank = 2 GROUP BY department_id; ``` --- #### **3. Products with Consecutive Sales Increases** **Question:** Given a `sales` table with `product_id`, `sale_date`, and `amount`, find products with at least three consecutive days of increasing sales. **Solution Explanation:** Use `LAG()` to compare each day’s `amount` to the previous two days and identify consecutive increases. ```sql SELECT product_id, sale_date, amount FROM ( SELECT product_id, sale_date, amount, LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_day_amount, LAG(amount, 2) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_two_day_amount FROM sales ) trends WHERE amount > prev_day_amount AND prev_day_amount > prev_two_day_amount; ``` --- #### **4. Self-Reporting Employees** **Question:** In an `employees` table where each employee has an `employee_id` and `manager_id`, find employees who directly or indirectly report to themselves. **Solution Explanation:** Use a recursive CTE to identify cyclic reporting relationships where an employee appears as their own manager. ```sql WITH RECURSIVE report_chain AS ( SELECT employee_id, manager_id FROM employees WHERE employee_id = manager_id UNION SELECT e.employee_id, e.manager_id FROM employees e JOIN report_chain r ON e.manager_id = r.employee_id ) SELECT DISTINCT employee_id FROM report_chain WHERE employee_id = manager_id; ``` --- #### **5. Top 3 Products by Sales Per Month** **Question:** In a `sales` table with `product_id`, `sale_date`, and `amount`, find the top 3 products by sales amount for each month. **Solution Explanation:** Rank products by total monthly sales using `RANK()`, then filter for the top 3 products. ```sql SELECT product_id, sale_month, total_sales FROM ( SELECT product_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(amount) AS total_sales, RANK() OVER (PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY SUM(amount) DESC) AS product_rank FROM sales GROUP BY product_id, DATE_TRUNC('month', sale_date) ) ranked_sales WHERE product_rank <= 3; ``` --- These questions involve key SQL concepts, such as window functions (`LAG`, `RANK`, `DENSE_RANK`), recursive CTEs, and subqueries, which are essential for handling complex data patterns. Each question challenges logical thinking and SQL proficiency, making them suitable for evaluating advanced SQL skills.
avatar

Senior Product Analyst

Interviewed at Apna (India)

3.4★
Nov 4, 2024

Here’s a set of advanced SQL questions with brief explanations of the solutions. These questions focus on analyzing data and applying logical thinking using SQL. --- ### Complex SQL Questions and Solutions with Brief Explanations #### **1. Consecutive Sessions Without Breaks** **Question:** You have a `user_sessions` table with columns `user_id`, `login_time`, and `logout_time`. Find users who had consecutive sessions with no breaks (i.e., `logout_time` of one session equals `login_time` of the next). **Solution Explanation:** Use a self-join on `user_sessions` to find records where `logout_time` matches the `login_time` of the next session for the same user. ```sql SELECT a.user_id, a.login_time AS first_login, a.logout_time AS first_logout, b.login_time AS second_login FROM user_sessions a JOIN user_sessions b ON a.user_id = b.user_id AND a.logout_time = b.login_time; ``` --- #### **2. Second Highest Salary in Each Department** **Question:** In an `employees` table with columns `employee_id`, `department_id`, and `salary`, find the second-highest salary in each department. **Solution Explanation:** Use `DENSE_RANK()` in a subquery to rank salaries within each department, then filter to keep only the second-highest rank. ```sql SELECT department_id, MAX(salary) AS second_highest_salary FROM ( SELECT department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees ) ranked_salaries WHERE salary_rank = 2 GROUP BY department_id; ``` --- #### **3. Products with Consecutive Sales Increases** **Question:** Given a `sales` table with `product_id`, `sale_date`, and `amount`, find products with at least three consecutive days of increasing sales. **Solution Explanation:** Use `LAG()` to compare each day’s `amount` to the previous two days and identify consecutive increases. ```sql SELECT product_id, sale_date, amount FROM ( SELECT product_id, sale_date, amount, LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_day_amount, LAG(amount, 2) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_two_day_amount FROM sales ) trends WHERE amount > prev_day_amount AND prev_day_amount > prev_two_day_amount; ``` --- #### **4. Self-Reporting Employees** **Question:** In an `employees` table where each employee has an `employee_id` and `manager_id`, find employees who directly or indirectly report to themselves. **Solution Explanation:** Use a recursive CTE to identify cyclic reporting relationships where an employee appears as their own manager. ```sql WITH RECURSIVE report_chain AS ( SELECT employee_id, manager_id FROM employees WHERE employee_id = manager_id UNION SELECT e.employee_id, e.manager_id FROM employees e JOIN report_chain r ON e.manager_id = r.employee_id ) SELECT DISTINCT employee_id FROM report_chain WHERE employee_id = manager_id; ``` --- #### **5. Top 3 Products by Sales Per Month** **Question:** In a `sales` table with `product_id`, `sale_date`, and `amount`, find the top 3 products by sales amount for each month. **Solution Explanation:** Rank products by total monthly sales using `RANK()`, then filter for the top 3 products. ```sql SELECT product_id, sale_month, total_sales FROM ( SELECT product_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(amount) AS total_sales, RANK() OVER (PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY SUM(amount) DESC) AS product_rank FROM sales GROUP BY product_id, DATE_TRUNC('month', sale_date) ) ranked_sales WHERE product_rank <= 3; ``` --- These questions involve key SQL concepts, such as window functions (`LAG`, `RANK`, `DENSE_RANK`), recursive CTEs, and subqueries, which are essential for handling complex data patterns. Each question challenges logical thinking and SQL proficiency, making them suitable for evaluating advanced SQL skills.

Viewing 371 - 380 interview questions

Glassdoor has 519 interview questions and reports from Senior product analyst interviews. Prepare for your interview. Get hired. Love your job.