Skip to content

Basic Aggregate Functions

Table of Contents

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

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

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

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

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;

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

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;

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

1174. Immediate Food Delivery II

550. Game Play Analysis IV