Q.77-LEETCODE 1757

1757. Recyclable and Low Fat Products
      find the ids of products that are both low fat and recyclable.

SELECT product_id
FROM PRODUCTS
WHERE low_fats = 'Y' 
      AND recyclable = 'Y'

Q.78-LEETCODE 584

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

SELECT NAME
FROM CUSTOMER
WHERE REFEREE_ID <> 2
        OR REFEREE_ID IS NULL;

Q.79-LEETCODE 595

595. Big Countries
     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.

SELECT NAME, POPULATION, `AREA`
FROM WORLD
WHERE AREA >= 3000000
    OR POPULATION >= 25000000

Q.80-LEETCODE 1148

1148. Article Views I
      find all the authors that viewed at least one of their own articles.
      Return the result table sorted by id in ascending order.

SELECT DISTINCT AUTHOR_ID AS ID
FROM VIEWS
WHERE AUTHOR_ID = VIEWER_ID
ORDER BY ID ASC

Q.81-LEETCODE 1683

1683. Invalid Tweets
      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.

SELECT TWEET_ID
FROM TWEETS
WHERE LENGTH(CONTENT) > 15

Q.82-LEETCODE 1378

1378. Replace Employee ID With The Unique Identifier
      show the unique ID of each user, If a user does not have a unique ID replace just show null.

SELECT U.UNIQUE_ID, E.NAME
FROM EMPLOYEES E
LEFT JOIN EMPLOYEEUNI U USING (ID)

Q.83-LEETCODE 1068

1068. Product Sales Analysis I
      report the product_name, year, and price for each sale_id in the Sales table.

SELECT p.PRODUCT_NAME, s.YEAR, s.PRICE
FROM SALES s
JOIN PRODUCT p
USING (PRODUCT_ID)

Q.85-LEETCODE 197 ✅

197. Rising Temperature
     find all dates' Id with higher temperatures compared to its previous dates (yesterday).

SELECT w2.id
FROM WEATHER w1
JOIN WEATHER w2
WHERE w2.temperature > w1.temperature
    AND DATEDIFF(w2.recorddate,w1.recorddate) = 1

Q.89-LEETCODE 570

570. Managers with at Least 5 Direct Reports
     find managers with at least five direct reports.
     

SELECT e1.name
FROM employee e1
JOIN employee e2
ON e1.id = e2.managerId
HAVING count(e1.id) >= 5

Q.91-LEETCODE 620

620. Not Boring Movies
     report the movies with an odd-numbered ID and a description that is not "boring".
     ordered by rating in descending order.

SELECT *
FROM cinema
WHERE (description <> 'boring'
    AND id % 2 = 1)
ORDER BY rating desc

Q.92-LEETCODE 1251 ✅

1251. Average Selling Price
      find the average selling price for each product. average_price should be rounded to 2 decimal places. For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.

• 이 문제의 핵심은 prices 테이블에 있는 가격 정보와 판매된 상품의 수를 통해 판매된 상품의 평균 가격을 구하는 것이다.
• product_id 3 같은 경우 판매 되지 않았지만, 그에 대한 결과 값은 출력되어야 한다.
• 해당 데이터는 IFNULL로 처리
• 각 상품의 가격은 판매 일자가 가격 테이블에서 시작-종료 일자 사이에 해당하는 가격과 매핑된다.

SELECT p.product_id
	, IFNULL(ROUND(SUM(p.price * u.units) / 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;

Q.93-LEETCODE 1075

1075. Project Employees I
      the average experience years of all the employees for each project, rounded to 2 digits.
      

SELECT p.project_id
     , round(avg(experience_years),2) average_years 
FROM PROJECT p
JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY p.project_id

Q.94-LEETCODE 1633

1633. Percentage of Users Attended a Contest
      find the percentage of the users registered in each contest rounded to two decimals.
      ordered by percentage in descending order. In case of a tie, order it by contest_id in ascending order.
      

SELECT contest_id
     , round(
        count(distinct user_id) * 100 /
        (SELECT COUNT(*) FROM users), 2) as percentage
FROM register 
GROUP BY contest_id
ORDER BY 2 desc, 1

Q.95-LEETCODE 1211 ✅

1211. Queries Quality and Percentage
      find each query_name, the quality and poor_query_percentage.
      Both quality and poor_query_percentage should be rounded to 2 decimal places.
      

SELECT 
    query_name,
    ROUND(AVG(rating / `position`), 2) AS quality,
    ROUND(
        (100.0 * SUM(IF(rating < 3, 1, 0))) / COUNT(*), 2
    ) AS poor_query_percentage
FROM 
    queries
WHERE 
    query_name IS NOT NULL
GROUP BY 
    query_name;

• 비율을 계산할 때, 100 대신 100.0을 사용해 명확하게 소수점을 포함하여 계산