Basic Aggregate Functions

Table of Contents

• 620. Not Boring Movies (Easy)
• 1251. Average Selling Price (Easy)
• 1075. Project Employees I (Easy)
• 1633. Percentage of Users Attended a Contest (Easy)
• 1211. Queries Quality and Percentage (Easy)
• 1193. Monthly Transactions I (Medium)
• 1174. Immediate Food Delivery II (Medium)
• 550. Game Play Analysis IV (Medium)

620. Not Boring Movies

• LeetCode | LeetCode CH (Easy)

• Tags: database

  620. Not Boring Movies

  SELECT
      id,
      movie,
      description,
      rating
  FROM
      cinema
  WHERE
      id % 2 = 1
      AND LOWER(description) NOT LIKE '%boring%'
  ORDER BY
      rating DESC;
  

1251. Average Selling Price

• LeetCode | LeetCode CH (Easy)

• Tags: database

  1251. Average Selling Price

  SELECT
      p.product_id,
      COALESCE(
          ROUND(SUM(p.price * u.units) / SUM(u.units), 2),
          0
      ) AS average_price
  FROM
      prices AS p
  LEFT JOIN unitssold AS 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;
  

1075. Project Employees I

• LeetCode | LeetCode CH (Easy)

• Tags: database

  1075. Project Employees I

  SELECT
      p.project_id,
      ROUND(AVG(e.experience_years), 2) AS average_years
  FROM
      project AS p
  LEFT JOIN employee AS e ON p.employee_id = e.employee_id
  GROUP BY
      p.project_id
  

1633. Percentage of Users Attended a Contest

• LeetCode | LeetCode CH (Easy)

• Tags: database

  1633. Percentage of Users Attended a Contest

  SELECT
      r.contest_id,
      ROUND(
          COUNT(DISTINCT r.user_id) / COUNT(DISTINCT u.user_id) * 100,
          2
      ) AS percentage
  FROM
      users AS u
  CROSS JOIN register AS r
  GROUP BY
      r.contest_id
  ORDER BY
      percentage DESC,
      contest_id ASC
  

1211. Queries Quality and Percentage

• LeetCode | LeetCode CH (Easy)

• Tags: database

  1211. Queries Quality and Percentage

  Input:
  Queries table:
  +------------+-------------------+----------+--------+
  | query_name | result            | position | rating |
  +------------+-------------------+----------+--------+
  | Dog        | Golden Retriever  | 1        | 5      |
  | Dog        | German Shepherd   | 2        | 5      |
  | Dog        | Mule              | 200      | 1      |
  | Cat        | Shirazi           | 5        | 2      |
  | Cat        | Siamese           | 3        | 3      |
  | Cat        | Sphynx            | 7        | 4      |
  +------------+-------------------+----------+--------+
  Output:
  +------------+---------+-----------------------+
  | query_name | quality | poor_query_percentage |
  +------------+---------+-----------------------+
  | Dog        | 2.50    | 33.33                 |
  | Cat        | 0.66    | 33.33                 |
  +------------+---------+-----------------------+
  

1211. Queries Quality and Percentage

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;

1193. Monthly Transactions I

• LeetCode | LeetCode CH (Medium)

• Tags: database

  1193. Monthly Transactions I

  Input:
  Transactions table:
  +------+---------+----------+--------+------------+
  | id   | country | state    | amount | trans_date |
  +------+---------+----------+--------+------------+
  | 121  | US      | approved | 1000   | 2018-12-18 |
  | 122  | US      | declined | 2000   | 2018-12-19 |
  | 123  | US      | approved | 2000   | 2019-01-01 |
  | 124  | DE      | approved | 2000   | 2019-01-07 |
  +------+---------+----------+--------+------------+
  Output:
  +----------+---------+-------------+----------------+--------------------+-----------------------+
  | month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
  +----------+---------+-------------+----------------+--------------------+-----------------------+
  | 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
  | 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
  | 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
  +----------+---------+-------------+----------------+--------------------+-----------------------+
  

1193. Monthly Transactions I

SELECT
    country,
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    COUNT(*) AS trans_count,
    COUNT(IF(state = 'approved', 1, NULL)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM
    transactions
GROUP BY
    month,
    country;

1174. Immediate Food Delivery II

• LeetCode | LeetCode CH (Medium)

• Tags: database

550. Game Play Analysis IV

• LeetCode | LeetCode CH (Medium)

• Tags: database

Comments