Mechanical Data Analyst

 SQL Leetcode Question No.  177 .  Nth Highest Salary Hello All, We are back with another SQL problem to gain understanding about SQL. This blog-post focuses on SQL user defined function. To know more about SQL user defined function, I highly recommend this blog-post. Learn SQL: User-Defined Functions (sqlshack.com) Give a try to solve below problem. Solution is provided as usual. Feel free to comment if any queries and also suggest alternate approach to solve this problem. Have a Happy Learning !   SQL Schema Table:  Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the primary key column for this table. Each row of this table contains information about the salary of an employee.   Write an SQL query to report the  n th  highest salary from the  Employee  table. If there is no  n th  highest salary, the query should repo...

180 .  Consecutive Numbers   Hello All, I am back with another SQL leetcode solution. This time we are going one step ahead and practice question with medium difficulty. SQL Schema Table:  Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the primary key for this table. id is an autoincrement column.   Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ Output: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ Explanation: 1 is the only number that appears consecutively for at least three ti...

Leetcode question no. 181   181 .  Employees Earning More Than Their Managers SQL Schema Table:  Employee +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | salary | int | | managerId | int | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.   Write an SQL query to find the employees who earn more than their managers. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Employee table: +----+-------+--------+-----------+ | id | name | salary | managerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | Null | | 4 | Max | 90000 | Null | +----+-------+--------+-----------+ Output: ...

Answer to SQL Leetcode question 1757  1757 .  Recyclable and Low Fat Products SQL Schema Table:  Products +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | low_fats | enum | | recyclable | enum | +-------------+---------+ product_id is the primary key for this table. low_fats is an ENUM of type ('Y', 'N') where 'Y' means this product is low fat and 'N' means it is not. recyclable is an ENUM of types ('Y', 'N') where 'Y' means this product is recyclable and 'N' means it is not.   Write an SQL query to find the ids of products that are both low fat and recyclable. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Products table: +-------------+----------+------------+ | product_id | low_fats | recyclable | +-------------+----------+------------+ | 0 | Y | N | | 1 ...

Answer to SQL Leetcode question no. 584  Question 584 SQL Schema Table:  Customer +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | referee_id | int | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.   Write an SQL query to report the names of the customer that are  not referred by  the customer with  id = 2 . Return the result table in  any order . The query result format is in the following example. Example 1: Input: Customer table: +----+------+------------+ | id | name | referee_id | +----+------+------------+ | 1 | Will | null | | 2 | Jane | null | | 3 | Alex | 2 | | 4 | Bill | null | | 5 | Zack | 1 | | 6 | Mark | 2 | +----+------+------------+ Output: +------+ | name | +----...