Mechanical Data Analyst

  Hello All, We are back with another SQL problem, focusing on use case of SQL CASE statement. CASE statement works similar to that of IF - ELSE ladder in other programming languages. This problem requires use of CONCAT() function. You can visit following learning resources related to this particular question : 1. SQL CASE statement -  SQL CASE Statement (w3schools.com) 2. concat function -  CONCAT (Transact-SQL) - SQL Server | Microsoft Docs   The problem below is sourced from HackerRank. You can visit the link:  Binary Tree Nodes | HackerRank 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 !   You are given a table,  BST , containing two columns:  N  and  P,  where  N  represents the value of a node in  Binary Tree , and  P  is the parent of  N . Write a query to ...

 Hello All, We are back with another SQL problem to gain understanding about SQL. This blog-post focuses on implementation of sub-queries in FROM clause. The problem below is sourced from Leetcode. You can visit the link:  Department Top Three Salaries - LeetCode To know more about SQL sub-query in FROM clause , I highly recommend this blog-post. SQL | Sub queries in From Clause - GeeksforGeeks 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 | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id is the primary key column for this table. departmentId is a foreign key of the ID from the Department table. Each row of this table indicates th...

 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...

Leetcode Problem No. 178  Hello All, We are back with another SQL problem to gain understanding about SQL. This blog-post focuses on SQL window functions, understanding difference between 2 SQL functions RANK() and DENSE_RANK(). 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 !   178 .  Rank Scores SQL Schema Table:  Scores +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | score | decimal | +-------------+---------+ id is the primary key for this table. Each row of this table contains the score of a game. Score is a floating point value with two decimal places.   Write an SQL query to rank the scores. The ranking should be calculated according to the following rules: The scores should be ranked from the highest to the lowest. If there is a tie between two scores, ...

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: ...