sql

Question 1

Given the table Products:

\+ -------------+---------+
\+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
\+-------------+---------+

Write a solution to find the ids of products that are both low fat and recyclable.

Return the result table in any order.

Column Name | Type |

Answer 1

SELECT product_id from products where low_fats = 'Y' and recyclable = 'Y';

Question 2

Table: Customer

\+-------------+---------+
\+-------------+---------+
| id          | int     |
| name        | varchar |
| referee_id  | int     |
\+-------------+---------+

In SQL, 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.

Find the names of the customer that are not referred by the customer with id = 2.

Column Name | Type |

Answer 2

SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;

Question 3

A country is big if:

it has an area of at least three million (i.e., 3000000 km2), or
it has a population of at least twenty-five million (i.e., 25000000).
Write a solution to find the name, population, and area of the big countries.

Return the result table in any order.

The result format is in the following example.

Answer 3

SELECT name, population, area from world where area >= 3000000 or population >= 25000000;

Question 4

Write a solution to find all the authors that viewed at least one of their own articles.

Answer 4

select distinct(author_id) as id from Views where author_id = viewer_id order by author_id asc;

Question 5

Write a solution to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15.

Answer 5

SELECT tweet_id FROM Tweets
WHERE LENGTH(content) > 15;

Question 6

Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.

Return the result table in any order.

Answer 6

SELECT eu.unique_id, e.name from Employees e left join EmployeeUNI eu on e.id = eu.id;

Question 7

Table: Sales

\+-------------+-------+
\+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
\+-------------+-------+

Table: Product

\+--------------+---------+
| Column Name  | Type    |
\+--------------+---------+
| product_id   | int     |
| product_name | varchar |
\+--------------+---------+

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Return the resulting table in any order.

Column Name | Type |

Answer 7

select p.product_name, s.year, s.price 
from sales s
join product p 
on s.product_id = p.product_id;

Question 8

Table: Visits

\+-------------+---------+
\+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
\+-------------+---------+

Table: Transactions

\+----------------+---------+
| Column Name    | Type    |
\+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
\+----------------+---------+

Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.

Column Name | Type |

Answer 8

select v.customer_id, count(v.visit_id) as count_no_trans from Visits v
Left join Transactions t
on v.visit_id = t.visit_id
where t.transaction_id is null 
group by v.customer_id

Question 9

Table: Weather

\+---------------+---------+
\+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
\+---------------+---------+

Write a solution to find all dates’ id with higher temperatures compared to its previous dates (yesterday).

Column Name | Type |

Answer 9

~~~

```SELECT today.id
FROM Weather yesterday
CROSS JOIN Weather today

WHERE DATEDIFF(today.recordDate,yesterday.recordDate) = 1
AND today.temperature > yesterday.temperature

Question 10

Table: Activity

\+----------------+---------+
\+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
\+----------------+---------+

There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.

Column Name | Type |

Answer 10

select 
a.machine_id,
round(
      (select avg(a1.timestamp) from Activity a1 where a1.activity_type = 'end' and a1.machine_id = a.machine_id) - 
      (select avg(a1.timestamp) from Activity a1 where a1.activity_type = 'start' and a1.machine_id = a.machine_id)
,3) as processing_time
from Activity a
group by a.machine_id

Question 11

Table: Employee

\+-------------+---------+
\+-------------+---------+
| empId       | int     |
| name        | varchar |
| supervisor  | int     |
| salary      | int     |
\+-------------+---------+

Table: Bonus

\+-------------+------+
| Column Name | Type |
\+-------------+------+
| empId       | int  |
| bonus       | int  |
\+-------------+------+

Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.

Column Name | Type |

Answer 11

SELECT Employee.name,Bonus.bonus 
FROM Employee 
LEFT JOIN Bonus 
ON Employee.empID = Bonus.empID
WHERE bonus < 1000 OR Bonus IS NULL ;

Question 12

Table: Students

\+---------------+---------+
\+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
\+---------------+---------+

Table: Subjects

\+--------------+---------+
| Column Name  | Type    |
\+--------------+---------+
| subject_name | varchar |
\+--------------+---------+

Table: Examinations

\+--------------+---------+
| Column Name  | Type    |
\+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
\+--------------+---------+

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

| Column Name | Type |

Column Name | Type |

Answer 12

SELECT
    S.student_id
    ,S.student_name
    ,Su.subject_name
    ,COUNT(E.student_id) attended_exams

FROM Students S
CROSS JOIN Subjects Su
LEFT JOIN Examinations E
    ON S.student_id = E.student_id
    AND Su.subject_name = E.subject_name
GROUP BY S.student_id, S.student_name, Su.subject_name

ORDER BY S.student_id, S.student_name, Su.subject_name

Question 13

Table: Employee

\+-------------+---------+
\+-------------+---------+
| name        | varchar |
| department  | varchar |
| managerId   | int     |
\+-------------+---------+

Write a solution to find managers with at least five direct reports.

| id | int |

Column Name | Type |

Answer 13

SELECT a.name 
FROM Employee a 
JOIN Employee b ON a.id = b.managerId 
GROUP BY b.managerId 
HAVING COUNT(*) >= 5

Question 14

Table: Signups

\+----------------+----------+
\+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
\+----------------+----------+

Table: Confirmations

\+----------------+----------+
| Column Name    | Type     |
\+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
\+----------------+----------+

The confirmation rate of a user is the number of ‘confirmed’ messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write a solution to find the confirmation rate of each user.

Column Name | Type |

Answer 14

select s.user_id, round(
avg(
if(
c.action="confirmed",1,0
)
),2) as confirmation_rate
from Signups as s 
left join Confirmations as c 
on s.user_id= c.user_id 
group by user_id;

Question 15

Table: Cinema

\+----------------+----------+
\+----------------+----------+
| id             | int      |
| movie          | varchar  |
| description    | varchar  |
| rating         | float    |
\+----------------+----------+

Column Name | Type |

Answer 15

Select * from Cinema
where MOD(id, 2) != 0 and description != ‘boring’
order by rating desc;

Question 16

Table: Prices

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
\+---------------+---------+

Table: UnitsSold

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
\+---------------+---------+

Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.

Answer 16

SELECT 
    p.product_id, 
    IFNULL(ROUND(SUM(u.units * p.price)/SUM(u.units), 2),0) as average_price
FROM Prices p 
LEFT JOIN UnitsSold u
ON 
    p.product_id = u.product_id
    AND u.purchase_date BETWEEN p.start_date and p.end_date
GROUP BY 
    p.product_id

Question 17

Table: Project
\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| project_id  | int     |
| employee_id | int     |
\+-------------+---------+
Table: Employee

\+------------------+---------+
| Column Name      | Type    |
\+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
\+------------------+---------+

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

Answer 17

SELECT p.project_id, ROUND(AVG(e.experience_years), 2) as average_years
FROM Project p 
JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY p.project_id

Question 18

Table: Users

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| user_id     | int     |
| user_name   | varchar |
\+-------------+---------+

Table: Register

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| contest_id  | int     |
| user_id     | int     |
\+-------------+---------+

Write a solution to find the percentage of the users registered in each contest rounded to two decimals.

Answer 18

select
contest_id, round(count(distinct user_id)* 100/(select count(user_id) from Users), 2) as percentage
from Register
group by contest_id
order by percentage desc, contest_id

Question 19

Table: Queries

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
\+-------------+---------+

We define query quality as:

The average of the ratio between query rating and its position.

We also define poor query percentage as:

The percentage of all queries with rating less than 3.

Write a solution to find each query_name, the quality and poor_query_percentage.

Answer 19

SELECT 
    query_name,
    ROUND(AVG(rating/position), 2) AS quality,
    ROUND(SUM(IF(rating < 3, 1, 0)) / COUNT(*) * 100, 2) AS poor_query_percentage
FROM Queries
where query_name is not null
GROUP BY query_name;

Question 20

Table: Transactions

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
\+---------------+---------+

Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

Answer 20

select 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    count(id) as trans_count,
    sum(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) as approved_count, 
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
    FROM Transactions
    group by month, country;

Question 21

Table: Delivery

\+-----------------------------+---------+
| Column Name                 | Type    |
\+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
\+-----------------------------+---------+

If the customer’s preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.

The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.

Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

Answer 21

SELECT 
    ROUND(SUM(
        CASE 
        WHEN order_date = customer_pref_delivery_date 
        THEN 1  ELSE 0 END
        ) * 100.0 / COUNT(DISTINCT customer_id), 2) 
        AS immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id, MIN(order_date) AS first_order_date
    FROM Delivery
    GROUP BY customer_id
);

Question 22

Table: Activity

\+--------------+---------+
| Column Name  | Type    |
\+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
\+--------------+---------+

Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

Answer 22

WITH 
    a2 AS (SELECT player_id, MIN(event_date) AS min_date FROM Activity
    GROUP BY player_id)

SELECT ROUND(COUNT(a1.player_id) / (SELECT COUNT(player_id) FROM a2), 2) AS fraction
FROM Activity a1 JOIN a2 ON a1.player_id = a2.player_id AND a2.min_date = DATE_SUB(a1.event_date, INTERVAL 1 DAY);

Question 23

Table: Teacher

\+-------------+------+
\+-------------+------+
\+-------------+------+

Write a solution to calculate the number of unique subjects each teacher teaches in the university.

| subject_id | int |

| dept_id | int |

| Column Name | Type |

teacher_id | int |

Answer 23

select 
    teacher_id, 
    count(distinct subject_id) as cnt 
from Teacher 
group by teacher_id;

Question 24

Table: Activity

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
\+---------------+---------+

Write a solution to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.

Answer 24

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity 
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY 1;

Question 25

Table: Sales

\+-------------+-------+
| Column Name | Type  |
\+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
\+-------------+-------+

Table: Product

\+--------------+---------+
| Column Name  | Type    |
\+--------------+---------+
| product_id   | int     |
| product_name | varchar |
\+--------------+---------+

Write a solution to select the product id, year, quantity, and price for the first year of every product sold.

Answer 25

with sale as 
(select *,rank() over (partition by product_id order by year) as rw
from sales)

select product_id,year as first_year,quantity, price 
from sale
where rw=1;

Question 26

Table: Courses

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| student     | varchar |
| class       | varchar |
\+-------------+---------+

Write a solution to find all the classes that have at least five students.

Answer 26

select class from courses group by class having count(class) >= 5;

Question 27

Table: Followers

\+-------------+------+
| Column Name | Type |
\+-------------+------+
| user_id     | int  |
| follower_id | int  |
\+-------------+------+

Write a solution that will, for each user, return the number of followers.

Return the result table ordered by user_id in ascending order.

Answer 27

select user_id, count(distinct(follower_id)) as followers_count from Followers group by user_id order by user_id asc;

Question 28

Table: MyNumbers

\+-------------+------+
| Column Name | Type |
\+-------------+------+
| num         | int  |
\+-------------+------+

A single number is a number that appeared only once in the MyNumbers table.

Find the largest single number. If there is no single number, report null.

Answer 28

SELECT MAX(num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) = 1
) AS unique_numbers;

Question 29

Table: Customer

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| customer_id | int     |
| product_key | int     |
\+-------------+---------+

Table: Product

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| product_key | int     |
\+-------------+---------+

Write a solution to report the customer ids from the Customer table that bought all the products in the Product table.

Answer 29

select customer_id 
From Customer
group by customer_id
having count(distinct product_key) = (
    SELECT
        COUNT(product_key)
        From 
        Product
);

Question 30

Table: Employees

\+-------------+----------+
| Column Name | Type     |
\+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| reports_to  | int      |
| age         | int      |
\+-------------+----------+

For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.

Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.

Return the result table ordered by employee_id.

employee_id | int |

Answer 30

select 
    e.employee_id,
    e.name,
    r.reports_count,
    r.average_age as average_age
from employees as e
inner join (
    select 
        reports_to, 
        count(*) as reports_count, 
        round(avg(age)) as average_age 
    from employees 
    group by reports_to
    ) as r on e.employee_id = r.reports_to
    order by e.employee_id;

Question 31

Table: Employee
\+---------------+---------+
\+---------------+---------+
employee_id   | int     |
| department_id | int     |
| primary_flag  | varchar |
\+---------------+---------+

Employees can belong to multiple departments. When the employee joins other departments, they need to decide which department is their primary department. Note that when an employee belongs to only one department, their primary column is ‘N’.

Write a solution to report all the employees with their primary department. For employees who belong to one department, report their only department.

Column Name | Type |

Answer 31

SELECT employee_id, department_id FROM Employee WHERE employee_id IN (
SELECT employee_id FROM Employee
GROUP BY employee_id HAVING COUNT(*) =1) OR primary_flag = 'Y'

Question 32

Table: Triangle

\+-------------+------+
| Column Name | Type |
\+-------------+------+
| x           | int  |
| y           | int  |
| z           | int  |
\+-------------+------+
In SQL, (x, y, z) is the primary key column for this table.
Each row of this table contains the lengths of three line segments.

Report for every three line segments whether they can form a triangle.

Answer 32

SELECT 
    x,
    y,
    z,
    CASE
        WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
        ELSE 'No'
    END AS 'triangle'
FROM
    triangle
;

Question 33

Table: Logs

\+-------------+---------+
Column Name | Type    |
\+-------------+---------+
| id          | int     |
| num         | varchar |
\+-------------+---------+
In SQL, id is the primary key for this table.
id is an autoincrement column starting from 1.

Find all numbers that appear at least three times consecutively.

Column Name | Type |

Answer 33

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

Question 34

Table: Products

product_id    | int     |
new_price    | int     |
| change_date   | date    |
\+---------------+---------+
(product_id, change_date) is the primary key (combination of columns with unique values) of this table.
Each row of this table indicates that the price of some product was changed to a new price at some date.

Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

| product_id | int |

| new_price | int |

Column Name | Type |

Answer 34

SELECT
  product_id,
  10 AS price
FROM
  Products
GROUP BY
  product_id
HAVING
  MIN(change_date) > '2019-08-16'
UNION ALL
SELECT
  product_id,
  new_price AS price
FROM
  Products
WHERE
  (product_id, change_date) IN (
    SELECT
      product_id,
      MAX(change_date)
    FROM
      Products
    WHERE
      change_date <= '2019-08-16'
    GROUP BY
      product_id
  )

Question 35

Table: Queue

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
\+-------------+---------+
person_id column contains unique values.

This table has the information about all people waiting for a bus.
The person_id and turn columns will contain all numbers from 1 to n, where n is the number of rows in the table.
turn determines the order of which the people will board the bus, where turn=1 denotes the first person to board and turn=n denotes the last person to board.
weight is the weight of the person in kilograms.

There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board.

Write a solution to find the person_name of the last person that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit.

Answer 35

# Write your MySQL query statement below
SELECT 
    q1.person_name
FROM Queue q1 JOIN Queue q2 ON q1.turn >= q2.turn
GROUP BY q1.turn
HAVING SUM(q2.weight) <= 1000
ORDER BY SUM(q2.weight) DESC
LIMIT 1

Question 36

Table: Accounts

\+-------------+------+
| Column Name | Type |
\+-------------+------+
| account_id  | int  |
| income      | int  |
\+-------------+------+

account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.

Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:

“Low Salary”: All the salaries strictly less than $20000.
“Average Salary”: All the salaries in the inclusive range [$20000, $50000].
“High Salary”: All the salaries strictly greater than $50000.
The result table must contain all three categories. If there are no accounts in a category, return 0.

Answer 36

SELECT 
    'Low Salary' AS category,
    SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts

Question 37

\+-------------+----------+
|col name | type |
\+-------------+----------+
|employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
\+-------------+----------+
In SQL, employee_id is the primary key for this table.
This table contains information about the employees, their salary, and the ID of their manager. Some employees do not have a manager (manager_id is null).

Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

Column Name | Type |

Answer 37

SELECT employee_id
FROM Employees
WHERE salary < 30000
AND manager_id IS NOT NULL
AND manager_id NOT IN (SELECT employee_id FROM Employees)
ORDER BY employee_id;

Question 38

Table: Seat
\+-------------+---------+
\+-------------+---------+
| id          | int     |
| student     | varchar |
\+-------------+---------+

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

Column Name | Type |

Answer 38

Write your MySQL query statement below

SELECT 
    id,
    CASE
        WHEN id % 2 = 0 THEN LAG(student) OVER(ORDER BY id)
        ELSE COALESCE(LEAD(student) OVER(ORDER BY id), student)
    END AS student
FROM Seat

Question 39

Movie Rating

Table: Movies

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| movie_id      | int     |
| title         | varchar |
\+---------------+---------+

Table: Users

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| user_id       | int     |
| name          | varchar |
\+---------------+---------+

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| movie_id      | int     |
| user_id       | int     |
| rating        | int     |
| created_at    | date    |
\+---------------+---------+

Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.

Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

something

Answer 39

(SELECT name AS results
FROM MovieRating JOIN Users USING(user_id)
GROUP BY name
ORDER BY COUNT(*) DESC, name
LIMIT 1)

UNION ALL

(SELECT title AS results
FROM MovieRating JOIN Movies USING(movie_id)
WHERE EXTRACT(YEAR_MONTH FROM created_at) = 202002
GROUP BY title
ORDER BY AVG(rating) DESC, title
LIMIT 1);

Question 40

Table: Customer

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
\+---------------+---------+

You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).

Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.

Return the result table ordered by visited_on in ascending order.

Answer 40

SELECT
    visited_on,
    (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount,
    ROUND(
        (
            SELECT SUM(amount) / 7
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        ),
        2
    ) AS average_amount
FROM customer c
WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )
GROUP BY visited_on;

Question 41

Table: RequestAccepted

\+----------------+---------+
| Column Name    | Type    |
\+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
\+----------------+---------+

Write a solution to find the people who have the most friends and the most friends number.

The test cases are generated so that only one person has the most friends.

Answer 41

# Write your MySQL query statement below
with base as(select requester_id id from RequestAccepted
union all
select accepter_id id from RequestAccepted)
select id, count(*) num  from base group by 1 order by 2 desc limit 1

Question 41

Table: Insurance

\+-------------+-------+
| Column Name | Type  |
\+-------------+-------+
| pid         | int   |
| tiv_2015    | float |
| tiv_2016    | float |
| lat         | float |
| lon         | float |
\+-------------+-------+

Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who:

have the same tiv_2015 value as one or more other policyholders, and
are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique).
Round tiv_2016 to two decimal places.

Answer 41

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)
AND (lat, lon) IN (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
)

Question 41

Table: Employee

\+--------------+---------+
| Column Name  | Type    |
\+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
\+--------------+---------+

Table: Department

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| id          | int     |
| name        | varchar |
\+-------------+---------+

Answer 41

SELECT department, employee, salary
FROM
(    
    SELECT
        dept.name AS department,
        emp.name AS employee,
        emp.salary AS salary,
        DENSE_RANK() OVER(PARTITION BY dept.name ORDER BY emp.salary DESC) AS unqrk
    FROM employee emp
    JOIN department dept ON dept.id = emp.departmentid
) AS table1
WHERE unqrk < 4;

Question 42

Table: Users
\+----------------+---------+
| Column Name    | Type    |
\+----------------+---------+
| user_id        | int     |
| name           | varchar |
\+----------------+---------+

Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.

Answer 42

# Write your MySQL query statement below
SELECT user_id,CONCAT(UPPER(SUBSTR(name,1,1)),LOWER(SUBSTR(name,2,length(name)))) AS name
FROM Users ORDER BY user_id;
# SUBSTR(string_name , start_index ,end_index)

second method 
SELECT user_id, concat(upper(LEFT(name, 1)), lower(RIGHT(name, length(name)-1))) as name
FROM users
ORDER BY user_id;
#RIGHT(name_of_string, no_of_charachters) 
===============   #👍Please do upvote if you got the solution ======================

Question 43

Table: Patients

\+--------------+---------+
| Column Name  | Type    |
\+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
\+--------------+---------+

patient_id is the primary key (column with unique values) for this table.
‘conditions’ contains 0 or more code separated by spaces.
This table contains information of the patients in the hospital.

Answer 43

select patient_id,patient_name,conditions from Patients
where conditions like 'DIAB1%'  or  conditions like '% DIAB1%' ;

Question 44

Table: Person

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| id          | int     |
| email       | varchar |
\+-------------+---------+

id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.

Answer 44

DELETE p1 FROM person p1,
    person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

Question 45

Table: Employee

\+-------------+------+
| Column Name | Type |
\+-------------+------+
| id          | int  |
| salary      | int  |
\+-------------+------+

Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

Answer 45

select
(select distinct Salary
from Employee order by salary desc
limit 1 offset 1)
as SecondHighestSalary;``

Question 46

Table Activities:

\+-------------+---------+
| Column Name | Type    |
\+-------------+---------+
| sell_date   | date    |
| product     | varchar |
\+-------------+---------+

There is no primary key (column with unique values) for this table. It may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.

Answer 46

select 
    sell_date, 
    count( distinct product ) as num_sold,
    GROUP_CONCAT( distinct product order by product ASC separator ',' ) as products
from Activities 
    group by sell_date 
    order by sell_date ASC;

Question 47

Table: Products

\+------------------+---------+
| Column Name      | Type    |
\+------------------+---------+
| product_id       | int     |
| product_name     | varchar |
| product_category | varchar |
\+------------------+---------+

Table: Orders

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| product_id    | int     |
| order_date    | date    |
| unit          | int     |
\+---------------+---------+

Write a solution to get the names of products that have at least 100 units ordered in February 2020 and their amount.

Column Name | Type |

Answer 47

select p.product_name, 
        sum(o.unit) as unit 
from Products p 
join Orders o 
    on p.product_id=o.product_id where o.order_date>='2020-02-01' and o.order_date<='2020-02-29'
group by o.product_id having unit>=100 

Question 48

Table: Users

\+---------------+---------+
| Column Name   | Type    |
\+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
\+---------------+---------+

 

Write a solution to find the users who have valid emails.

A valid e-mail has a prefix name and a domain where:

The prefix name is a string that may contain letters (upper or lower case), digits, underscore, period, and or dash.

The prefix name must start with a letter.

The domain is ‘@leetcode.com’.

Return the result table in any order.

Answer 48

SELECT *
FROM Users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_\.\-]*@leetcode(\\?com)?\\.com$';