SELECT 
    person1,person2, 
    count(*) call_count, 
    sum(duration) total_duration 
FROM 
(
    SELECT 
        IF(from_id>to_id, to_id, from_id) person1, 
        IF(from_id>to_id,from_id,to_id) person2, 
        duration  
    FROM 
        calls 
) c 
GROUP BY 
    person1, person2

SELECT
    product_id,
    Round(SUM(sales) / SUM(units), 2) AS average_price
FROM (
    SELECT
        Prices.product_id AS product_id,
        Prices.price * UnitsSold.units AS sales,
        UnitsSold.units AS units
    FROM 
        Prices JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
    WHERE UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date
) T
GROUP BY product_id

SELECT 
    w.name WAREHOUSE_NAME,
    SUM(p.Width * p.Length * p.Height * w.units) VOLUME
FROM 
    warehouse w
LEFT JOIN 
    products p
ON 
    w.product_id = p.product_id
GROUP BY w.name;

SELECT 
  DATE_FORMAT(trans_date, '%Y-%m') AS month, 
  country, 
  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

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

select 
    round (
        sum(order_date = customer_pref_delivery_date) / count(*) * 100,
        2
    ) as immediate_percentage
from Delivery

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

==

SELECT query_name
	, round(AVG(rating / position), 2) AS quality
	, round(100 * AVG(rating < 3), 2) AS poor_query_percentage
FROM Queries
GROUP BY query_name;

-- DATE_FORMAT / WHERE NOT IN

SELECT 
    seller_name AS SELLER_NAME
FROM 
    Seller
WHERE 
    Seller.seller_id NOT IN (
        SELECT DISTINCT seller_id AS id FROM Orders WHERE DATE_FORMAT(sale_date, "%Y") = 2020
    )
ORDER BY SELLER_NAME;

select 
    max(num) as num
from
    (
        select
            num
        from 
            MyNumbers
        group by num
        having count(num) = 1
    ) t

# 窗口
select 
    student_id, 
    course_id, 
    grade 
from 
    (
        select 
            *,
            dense_rank() over (partition by student_id order by grade desc, course_id) rk 
        from enrollments
    ) t
where 
    rk=1

select 
    o.customer_id customer_id,
    c.customer_name customer_name
 from 
     orders o JOIN customers c ON o.customer_id = c.customer_id
 group by o.customer_id 
 having 
    SUM(IF(o.product_name='A',1,0)) > 0 
    AND SUM(IF(o.product_name='B',1,0)) > 0 
    AND SUM(IF(o.product_name='C',1,0))=0