SELECT 
    DailySales.date_id,
    DailySales.make_name,
    COUNT(DISTINCT DailySales.lead_id) AS unique_leads,
    COUNT(DISTINCT DailySales.partner_id) AS unique_partners
FROM 
    DailySales
GROUP BY 
    DailySales.date_id,
    DailySales.make_name;

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

SELECT 
    Users.user_id,
    UPPER(LEFT(Users.name, 1)) 
        || LOWER(SUBSTRING(Users.name FROM 2)) AS name
FROM 
    Users
ORDER BY 
    Users.user_id;

SELECT 
    act1.machine_id, 
    ROUND(AVG(act2.timestamp - act1.timestamp)::decimal, 3) AS processing_time
FROM
    Activity act1,
    Activity act2
WHERE 
    act1.machine_id = act2.machine_id
    AND act1.process_id = act2.process_id
    AND act1.activity_type = 'start'
    AND act2.activity_type = 'end'
GROUP BY 
    act1.machine_id;

WITH total_users AS (
    SELECT 
        COUNT(DISTINCT user_id) AS total_count
    FROM 
        Users
)
SELECT 
    Register.contest_id,
    ROUND(
        COUNT(DISTINCT Register.user_id) * 100.0 / total_users.total_count,
        2
    ) AS percentage
FROM
    Register
CROSS JOIN 
    total_users
GROUP BY 
    Register.contest_id, 
    total_users.total_count
ORDER BY 
    percentage DESC, 
    Register.contest_id;

SELECT
    Users.name AS "NAME",
    SUM(Transactions.amount) AS "BALANCE"
FROM
    Users
LEFT JOIN
    Transactions
ON
    Users.account = Transactions.account
GROUP BY
    Users.account, Users.name
HAVING
    SUM(Transactions.amount) > 10000

SELECT
    Visits.customer_id,
    COUNT(Visits.visit_id) AS "count_no_trans"
FROM
    Visits
LEFT JOIN
    Transactions
ON
    Visits.visit_id = Transactions.visit_id
WHERE
    Transactions.visit_id IS NULL
GROUP BY
    Visits.customer_id

SELECT
    Patients.patient_id,
    Patients.patient_name,
    Patients.conditions
FROM
    Patients
WHERE
    Patients.conditions ~ '(^|\s)DIAB1'

SELECT 
    *
FROM 
    Users
WHERE 
    Users.mail ~ '^[a-zA-Z][a-zA-Z0-9\.\-_]*@leetcode\.com$';

SELECT 
    Activities.sell_date, 
    COUNT(
        DISTINCT (Activities.product, Activities.sell_date)
    ) AS num_sold, 
    STRING_AGG(
        DISTINCT Activities.product, ',' ORDER BY product
    ) AS products
FROM 
    Activities
GROUP BY 
    Activities.sell_date
ORDER BY 
    Activities.sell_date;

SELECT
    Users.name, 
    COALESCE(SUM(Rides.distance), 0) AS "travelled_distance"
FROM
    Users
LEFT JOIN
    Rides
ON
    Users.id = Rides.user_id
GROUP BY
    Users.id, Users.name
ORDER BY
    travelled_distance DESC,
    Users.name ASC

SELECT
    EmployeeUNI.unique_id, Employees.name
FROM
    Employees
LEFT JOIN
    EmployeeUNI
ON
    Employees.id = EmployeeUNI.id

SELECT 
    Products.product_name, SUM(Orders.unit) as "unit"
FROM 
    Products
JOIN 
    Orders
ON 
    Orders.product_id = Products.product_id
WHERE 
    Orders.order_date BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY 
    Products.product_name
HAVING 
    SUM(Orders.unit) >= 100;

SELECT
    Students.student_id, 
    Students.student_name,
    Subjects.subject_name,
    COUNT(Examinations.subject_name) as "attended_exams"
FROM
    Students
CROSS JOIN
    Subjects
LEFT JOIN
    Examinations
ON
    Students.student_id = Examinations.student_id
    AND Subjects.subject_name = Examinations.subject_name
GROUP BY
    Students.student_id, 
    Students.student_name, 
    Subjects.subject_name
ORDER BY
    Students.student_id, 
    Subjects.subject_name

SELECT 
    Prices.product_id,
    CASE
        WHEN 
            SUM(UnitsSold.units) is null
        THEN 
            0
        ELSE
            ROUND(SUM(UnitsSold.units * Prices.price) / SUM(UnitsSold.units)::numeric, 2)
    END AS average_price
FROM 
    Prices
LEFT JOIN 
    UnitsSold
ON 
    UnitsSold.product_id = Prices.product_id 
    AND UnitsSold.purchase_date 
        BETWEEN Prices.start_date AND Prices.end_date
GROUP BY 
    Prices.product_id;

SELECT 
    query_name,  
    ROUND(SUM(rating * 1.0 / position) / COUNT(*), 2) AS quality,
    ROUND(
        COUNT(CASE WHEN rating < 3 THEN 1 END) * 1.0 / COUNT(*), 
        4
    ) * 100 AS poor_query_percentage
FROM 
    Queries
GROUP BY 
    query_name;

SELECT id,
        SUM(
            CASE 
                WHEN 
                    month = 'Jan' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
            END
        ) AS Jan_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Feb' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
                END
        ) AS Feb_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Mar' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
                END
        ) AS Mar_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Apr' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
                END
        ) AS Apr_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'May' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
                END
        ) AS May_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Jun' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
                END
        ) AS Jun_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Jul'
                THEN 
                    revenue 
                ELSE 
                    NULL 
                END
        ) AS Jul_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Aug'
                THEN 
                    revenue 
                ELSE 
                    NULL
                END
        ) AS Aug_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Sep' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
                END
        ) AS Sep_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Oct' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
            END
        ) AS Oct_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Nov' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
            END
        ) AS Nov_Revenue,
        SUM(
            CASE 
                WHEN 
                    month = 'Dec' 
                THEN 
                    revenue 
                ELSE 
                    NULL 
            END
        ) AS Dec_Revenue
FROM 
    Department
GROUP BY 
    id;

SELECT DISTINCT
    views1.author_id as "id"
FROM
    Views views1
CROSS JOIN
    Views views2
WHERE
    views1.author_id = views2.viewer_id
    AND views1.author_id = views2.author_id
ORDER BY
    views1.author_id ASC

SELECT 
    activity_date AS day, 
    COUNT(DISTINCT(user_id)) AS active_users  
FROM 
    Activity 
WHERE 
    activity_date 
        BETWEEN '2019-07-27'::DATE - INTERVAL '29 DAYS' 
        AND '2019-07-27'::DATE
GROUP BY 
    activity_date

SELECT 
    Product.product_id, Product.product_name
FROM 
    Sales
JOIN 
    Product 
ON 
    Product.product_id = Sales.product_id
GROUP BY 
    Product.product_id, Product.product_name
HAVING
    MIN(Sales.sale_date) >= '2019-01-01' 
    AND MAX(Sales.sale_date) <= '2019-03-31'

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

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

SELECT
    product_name, year, price
FROM 
    Sales
LEFT JOIN
    Product
ON
    Sales.product_id = Product.product_id

SELECT 
    actor_id, director_id
FROM 
    ActorDirector
GROUP BY 
    actor_id, director_id
HAVING 
    COUNT(actor_id) >= 3;

UPDATE
    Salary
SET
    sex = (
        CASE
            WHEN
                sex = 'f'
            THEN
                'm'
            ELSE
                'f'
        END
    );

SELECT
    id, movie, description, rating
FROM
    Cinema
WHERE
    description != 'boring'
    AND id % 2 != 0
ORDER BY
    rating DESC;

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

SELECT
    x, y, z, ( 
        CASE
            WHEN 
                ((x+y+z) - GREATEST(x,y,z)) > GREATEST(x,y,z) 
            THEN 
                'Yes' 
            ELSE 
                'No'
        END
    ) AS triangle
FROM 
    Triangle;

(
    SELECT
        x, y, z, 'Yes' AS triangle
    FROM
        Triangle
    WHERE 
        (x + y > z) AND (y + z > x) AND (z + x > y)
) UNION (
    SELECT 
        x, y, z, 'No' AS triangle
    FROM
        Triangle
    WHERE (x + y <= z) OR (y + z <= x) OR (z + x <= y)
);

SELECT 
    sales_person.name
FROM (
    Orders orders
    JOIN 
        Company company
    ON 
        orders.com_id = company.com_id
        AND company.name = 'RED'
    RIGHT JOIN 
        SalesPerson sales_person
    ON 
        sales_person.sales_id = orders.sales_id
)
WHERE
    orders.sales_id IS NULL;

SELECT 
    class
FROM 
    Courses
GROUP BY 
    class
HAVING 
    COUNT(class) >= 5;

SELECT
    World.name, World.population, World.area
FROM
    World
WHERE
    World.area >= 3000000
    OR World.population >= 25000000;

SELECT 
    Orders.customer_number
FROM 
    Orders
GROUP BY 
    Orders.customer_number
ORDER BY 
    COUNT(Orders.customer_number) DESC
LIMIT 
    1;

SELECT 
    Orders.customer_number
FROM 
    Orders
GROUP BY 
    Orders.customer_number
ORDER BY 
    COUNT(*) DESC
LIMIT 
    1;

SELECT
    Customer.name
FROM 
    Customer
WHERE
    Customer.referee_id != 2
    OR Customer.referee_id IS NULL

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

SELECT
    player_id, MIN(event_date) as "first_login"
FROM
    Activity
GROUP BY
    player_id

DELETE FROM
    Person
WHERE 
    id NOT IN (
        SELECT MIN(id) FROM Person 
        GROUP BY email
    )
;

SELECT 
    today.id
FROM 
    Weather today
CROSS JOIN 
    Weather yesterday
WHERE 
    (today.recordDate - yesterday.recordDate = 1)
    AND today.temperature > yesterday.temperature

SELECT employee.name AS Employee
FROM Employee employee
JOIN Employee manager
ON employee.managerId = manager.id
WHERE employee.salary > manager.salary;

SELECT name as "Customers"
FROM Customers
WHERE id NOT IN (
    SELECT customerId 
    FROM Orders
);

SELECT email FROM Person
GROUP BY email
HAVING COUNT(email) > 1;

SELECT firstName, lastName, city, state 
FROM Person LEFT JOIN Address 
    ON Person.personId = Address.personId