select author_id as id from Views where author_id = viewer_id
group by author_id
order by author_id;

select distinct author_id as id from Views where author_id = viewer_id
order by author_id;

select eu.unique_id, e.name from Employees e left join EmployeeUNI
eu on e.id = eu.id;

SELECT EmployeeUNI.unique_id, Employees.name
FROM Employees NATURAL LEFT JOIN EmployeeUNI;

-- Query 1
SELECT customer_id, COUNT(visits.visit_id) AS count_no_trans
FROM visits
LEFT JOIN transactions USING(visit_id)
WHERE transactions.transaction_id IS NULL
GROUP BY 1;

-- Query 2
SELECT v.customer_id, COUNT(v.customer_id) AS count_no_trans 
FROM Visits v 
WHERE visit_id NOT IN (
    SELECT visit_id FROM Transactions
)
GROUP BY v.customer_id;

SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM Visits v
WHERE NOT EXISTS (
    SELECT 1 
    FROM Transactions t 
    WHERE t.visit_id = v.visit_id
)
GROUP BY v.customer_id;

SELEXT e.name, b.bonus from Employee e
LEFT JOIN Bonus b on b.empId = e.empId
WHERE b.bonus < 1000 or b.bonus is null

SELEXT e.name, b.bonus from Employee e
LEFT JOIN Bonus b on b.empId = e.empId
WHERE IFNULL(b.bonus, 0) < 1000;

SELECT s.student_id, s.student_name, sb.subject_name, count(e.student_id) as attended_exams
FROM Students s
CROSS JOIN Subjects sb
LEFT JOIN Examinations e ON e.student_id = s.student_id AND sb.subject_name = e.subject_name
group by 1,2,3
order by s.student_id, sb.subject_name

SELECT name 
FROM Employee 
WHERE id IN 
(SELECT managerId 
FROM Employee 
GROUP BY 
managerId 
HAVING 
COUNT(id) >= 5);

SELECT E1.name
FROM Employee E1 
LEFT JOIN Employee E2 on E1.id = E2.managerId
GROUP BY E1.id
HAVING count(E1.id) >= 5

SELECT 
    s.user_id,
    ROUND(AVG(IF(c.action="confirmed",1,0)),2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c on c.user_id = s.user_id
GROUP BY s.user_id

SELECT 
    s.user_id,
    ifnull(count(case when c.action = 'confirmed' then 1 end) / count(c.user_id),0) as confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c on c.user_id = s.user_id
GROUP BY s.user_id

SELECT 
    contest_id, 
    ROUND(count(user_id) * 100 / (select count(user_id) from Users) , 2) as percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage desc, contest_id

LEFT(trans_date, 7) AS month,
SUBSTR(trans_date, 1, 7) AS month,

SUM(state = 'approved') AS approved_count,
SUM((state = 'approved') * amount) AS approved_total_amount

# Write your MySQL query statement below
SELECT
DATE_FORMAT(trans_date, '%Y-%m') as month, 
country,
COUNT(id) as trans_count,
SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) as approved_count,
SUM(amount) as trans_total_amount,
SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) as approved_total_amount
FROM Transactions
GROUP BY month, country

Select * From Activity
Where (player_id, event_date) in (
select player_id, min(event_date)
FROM Activity
Group BY player_id

 Select 

ROUND(COUNT(player_id) / (SELECT COUNT(Distinct player_id) FROM Activity), 2) as fraction

From Activity
Where (player_id, DATE_SUB(event_date, INTERVAL 1 DAY) ) in (
select player_id, min(event_date)
FROM Activity
Group BY player_id
)

Select 
    teacher_id,
    COUNT(teacher_id) as cnt 
FROM(
    select teacher_id,subject_id
    from Teacher
    group by teacher_id, subject_id
) src
GROUP BY teacher_id

SELECT teacher_id, COUNT(DISTINCT subject_id) as cnt
FROM Teacher
GROUP BY teacher_id;

SELECT activity_date as day, COUNT(DISTINCT user_id) as active_users
FROM Activity
WHERE activity_date between DATE_SUB('2019-07-28', INTERVAL 1 MONTH) AND '2019-07-28'
GROUP BY activity_date

WIth
s1 as (Select * from Sales),
s2 as (
SELECT
    s.product_id, min(s.year) as year
FROM Sales s
GROUP BY product_id 
)
SELECT
    s1.product_id,
    s1.year as first_year,
    s1.quantity,
    s1.price
FROM s1 
JOIN s2 
WHERE
    s1.product_id = s2.product_id
AND s1.year = s2.year

select product_id,year as first_year, quantity, price
from Sales
where(product_id, year) in (select  product_id, min(year) from Sales group by product_id)

SELECT
    user_id,
    COUNT(follower_id) as followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id

SELECT
    CASE WHEN COUNT(num) = 1 THEN num
    ELSE null END AS num
FROM MyNumbers
GROUP BY num
ORDER BY COUNT(num), num desc
LIMIT 1

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

SELECT customer_id
FROM(
SELECT
   customer_id,product_key
FROM Customer
GROUP BY customer_id,product_key
) C1
GROUP BY customer_id
HAVING COUNT(customer_id) >= (
    SELECT COUNT(*) FROM Product
)

SELECT
   customer_id,product_key
FROM Customer
GROUP BY customer_id,product_key
HAVING COUNT(product_key) >= (
    SELECT COUNT(*) FROM Product
)

SELECT c.customer_id
FROM Customer c
GROUP BY c.customer_id
HAVING COUNT(DISTINCT c.product_key) = (SELECT COUNT(DISTINCT product_key) FROM Product);