SELECT product_idFROM ProductsWHERE low_fats='Y'AND recyclable='Y'

SELECT nameFROM CustomerWHERE referee_id!=2OR referee_id ISnull

SELECT name, population, areaFROM WORLDWHERE area>=3000000OR population>=25000000

SELECT DISTINCT author_idas idFROM ViewsWHERE viewer_id>=1AND author_id= viewer_idORDER BY author_id

SELECT tweet_idFROM TweetsWHERE length(content)>15

SELECT unique_id, nameFROM Employees eLEFT JOIN EmployeeUNI euONe.id=eu.id

SELECT product_name, year, priceFROM Sales sLEFT JOIN Product pONs.product_id=p.product_id

SELECT customer_id,COUNT(*)as count_no_transFROM VisitsWHERE visit_id NOTIN (SELECT DISTINCT visit_idFROM Transactions)GROUP BY customer_id

SELECTw1.idFROM Weather w1, Weather w2WHERE DATEDIFF(w1.recordDate,w2.recordDate)=1ANDw1.temperature>w2.temperature-- ORSELECTw1.idFROM Weather w1, Weather w2WHEREw1.temperature>w2.temperatureAND SUBDATE(w1.recordDate,1)=w2.recordDate

SELECT machine_id, ROUND(AVG(end- start),3)AS processing_timeFROM (SELECT machine_id, process_id,MAX(CASE WHEN activity_type='start' THENtimestamp END)AS start,MAX(CASE WHEN activity_type='end' THENtimestamp END)AS endFROM ActivityGROUP BY machine_id, process_id)AS subqGROUP BY machine_id

SELECT name, bonusFROM Employee eLEFT JOIN Bonus bONe.empId=b.empIdWHERE bonus<1000OR bonus ISNULL

SELECTa.student_id,a.student_name,b.subject_name,COUNT(c.subject_name)AS attended_examsFROM Students aJOIN Subjects bLEFT JOIN Examinations cONa.student_id=c.student_idANDb.subject_name=c.subject_nameGROUP BY1,3ORDER BY1,3

SELECT nameFROM EmployeeWHERE idIN  (SELECT managerIdFROM EmployeeGROUP BY managerIdHAVINGCOUNT(*)>=5  )-- ORSELECTa.nameFROM Employee aJOIN Employee bWHEREa.id=b.managerIdGROUP BYb.managerIdHAVINGCOUNT(*)>=5

SELECTs.user_id,   ROUND(    COALESCE(SUM(        CASE WHEN ACTION='confirmed' THEN1 END      )/COUNT(*),0),2)AS confirmation_rateFROM Signups sLEFT JOIN Confirmations cONs.user_id=c.user_idGROUP BYs.user_id;

-- odd id, "boring", rating descSELECT*FROM CinemaWHERE id %2<>0AND description<>"boring"ORDER BY ratingDESC

-- avg(selling), round 2SELECTp.product_id,   ROUND(SUM(price* units)/SUM(units),2)AS average_priceFROM Prices pLEFT JOIN UnitsSold sONp.product_id=s.product_idAND purchase_date BETWEEN start_dateAND end_dateGROUP BYp.product_id

-- avg(exp_yr), round 2, by projectSELECT project_id, ROUND(AVG(experience_years),2) average_yearsFROM Project pLEFT JOIN Employee eONp.employee_id=e.employee_idGROUP BY project_id

-- % desc, contest_id asc, round 2SELECTr.contest_id,       ROUND(COUNT(DISTINCTr.user_id)*100/ (SELECTCOUNT(DISTINCT user_id)FROM Users),2)AS percentageFROM Register rGROUP BYr.contest_idORDER BY percentageDESC,r.contest_idASC;

--quality - avg(rating/position), poor query % - %(rating < 3), round 2SELECT query_name,     ROUND(AVG(rating/position),2)AS quality,     ROUND(SUM(IF(rating<3,1,0))*100/COUNT(rating),2)AS poor_query_percentageFROM QueriesGROUP BY query_name-- ORSELECT query_name,     ROUND(AVG(rating/position),2)AS quality,     ROUND(SUM(        CASE WHEN rating<3 THEN1 ELSE0 END    )*100/COUNT(rating),2)AS poor_query_percentageFROM QueriesGROUP BY query_name

-- month, country, count(trans), total(amt), count(approved_trans), total(amt)SELECT DATE_FORMAT(trans_date,'%Y-%m') month, country,COUNT(state) trans_count,SUM(IF(state='approved',1,0)) approved_count,SUM(amount) trans_total_amount,SUM(IF(state='approved', amount,0)) approved_total_amountFROM TransactionsGROUP BY1,2-- ORSELECT DATE_FORMAT(trans_date,'%Y-%m') month, country,COUNT(state) trans_count,SUM(CASE WHEN state='approved' THEN1 ELSE0 END) approved_count,SUM(amount) trans_total_amount,SUM(CASE WHEN state='approved' THEN amount ELSE0 END) approved_total_amountFROM TransactionsGROUP BY1,2

SELECT    ROUND((COUNT(CASE WHENd.order_date=d.customer_pref_delivery_date THEN1 END)/COUNT(*))*100,2)  immediate_percentageFROM Delivery dWHEREd.order_date= (SELECTMIN(order_date)FROM DeliveryWHERE customer_id=d.customer_id    );-- ORSELECT ROUND(AVG(temp.order_date=temp.customer_pref_delivery_date)*100,2) immediate_percentageFROM (SELECT*, RANK() OVER(partition by customer_idORDER BY order_date) odFROM Delivery) tempWHEREtemp.od=1

WITH login_dateAS (SELECT player_id,MIN(event_date)AS first_loginFROM ActivityGROUP BY player_id),recent_loginAS (SELECT*, DATE_ADD(first_login, INTERVAL1 DAY)AS next_dayFROM login_date)SELECT ROUND((SELECTCOUNT(DISTINCT(player_id))FROM ActivityWHERE (player_id, event_date)IN (SELECT player_id, next_dayFROM recent_login))/ (SELECTCOUNT(DISTINCT player_id)FROM Activity),2)AS fraction

SELECT teacher_id,COUNT(DISTINCT subject_id) cntFROM TeacherGROUP BY teacher_id

SELECT activity_dateas day,COUNT(DISTINCT user_id)AS active_usersFROM ActivityWHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL29 DAY)AND'2019-07-27'GROUP BY activity_date

SELECTs.product_id,s.yearAS first_year,s.quantity,s.priceFROM Sales sJOIN (SELECT product_id,MIN(year)AS yearFROM salesGROUP BY product_id  ) pONs.product_id=p.product_idANDs.year=p.year-- ORWITH first_year_salesAS (SELECTs.product_id,MIN(s.year)as first_yearFROM Sales sINNER JOIN Product pONs.product_id=p.product_idGROUP BYs.product_id)SELECTf.product_id,f.first_year,s.quantity,s.priceFROM first_year_sales fJOIN Sales sONf.product_id=s.product_idANDf.first_year=s.year

SELECT classFROM CoursesGROUP BY classHAVINGCOUNT(student)>=5

SELECT user_id,COUNT(DISTINCT follower_id)AS followers_countFROM FollowersGROUP BY user_idORDER BY user_idASC

SELECT COALESCE(  (SELECT numFROM MyNumbersGROUP BY numHAVINGCOUNT(num)=1ORDER BY numDESCLIMIT1),null)AS num

SELECT customer_idFROM CustomerGROUP BY customer_idHAVINGCOUNT(DISTINCT product_key)= (SELECTCOUNT(product_key)FROM Product)

SELECTe1.employee_id,e1.name,COUNT(e2.employee_id) reports_count, ROUND(AVG(e2.age)) average_ageFROM Employees e1, Employees e2WHEREe1.employee_id=e2.reports_toGROUP BYe1.employee_idHAVING reports_count>0ORDER BYe1.employee_id

SELECT employee_id, department_idFROM EmployeeWHERE primary_flag='Y'UNIONSELECT employee_id, department_idFROM EmployeeGROUP BY employee_idHAVINGCOUNT(employee_id)=1-- ORSELECT employee_id,department_idFROM EmployeeWHERE primary_flag='Y'OR employee_idIN    (SELECT employee_idFROM employeeGROUP BY employee_idHAVINGCOUNT(department_id)=1    )

SELECT x, y, z, CASE WHEN x+ y> zAND x+ z> yAND y+ z> x THEN'Yes'ELSE'No' ENDAS triangleFROM Triangle

WITH cteAS (SELECT id, num,     LEAD(num) OVER (ORDER BY id)AS next,     LAG(num) OVER (ORDER BY id)AS prevFROM Logs)SELECT DISTINCT(num)AS ConsecutiveNumsFROM cteWHERE num= nextAND num= prev

SELECT product_id, new_priceAS priceFROM productsWHERE (product_id, change_date)IN(SELECT product_id,MAX(change_date)FROM productsWHERE change_date<='2019-08-16'GROUP BY product_id)UNIONSELECT product_id,10AS priceFROM productsWHEN product_id NOTIN(SELECT product_idFROM productsWHERE change_date<='2019-08-16')

SELECT employee_idFROM EmployeesWHERE manager_id NOTIN (SELECT employee_idFROM Employees)AND salary<30000ORDER BY employee_id

WITH RankedSalariesAS (SELECTe.IdAS employee_id,e.nameAS employee,e.salary,e.departmentId,    DENSE_RANK() OVER (PARTITION BYe.departmentIdORDER BYe.salaryDESC)AS salary_rankFROM Employee e)SELECTd.nameAS Department,r.employee,r.salaryFROM Department dJOIN RankedSalaries rONr.departmentId=d.idWHEREr.salary_rank<=3;

SELECT user_id, CONCAT(UPPER(LEFT(name,1)),LOWER(RIGHT(name, LENGTH(name)-1)))AS nameFROM UsersORDER BY user_id

SELECT patient_id, patient_name, conditionsFROM patientsWHERE conditionsLIKE'% DIAB1%'OR conditionsLIKE'DIAB1%'

DELETE pFROM Person p, Person qWHEREp.id>q.idANDq.Email=p.Email

SELECT(SELECT DISTINCT SalaryFROM EmployeeORDER BY SalaryDESCLIMIT1 OFFSET1)AS SecondHighestSalary-- HINT: subquery is used to return null if there is no SecondHighestSalary

SELECT*FROM UsersWHERE mail REGEXP'^[A-Za-z][A-Za-z0-9_\.\-]*@leetcode\\.com$'

-- 1000 kg limit-- name of last personWITH CTEAS (SELECT person_name, weight, turn,SUM(weight)     OVER(ORDER BY turn)AS total_weightFROM Queue)SELECT person_nameFROM cteWHERE total_weight<=1000ORDER BY total_weightDESCLIMIT1;

SELECT'Low Salary'AS category,SUM(IF(income<20000,1,0))AS accounts_countFROM AccountsUNIONSELECT'Average Salary'AS category,SUM(IF(income>=20000AND income<=50000,1,0))AS accounts_countFROM AccountsUNIONSELECT'High Salary'AS category,SUM(IF(income>50000,1,0))AS accounts_countFROM Accounts

-- id, student-- swap every two consecutives-- num(students): odd? no swap for last oneSELECT id, CASE WHEN MOD(id,2)=0 THEN (LAG(student) OVER (ORDER BY id))ELSE (LEAD(student,1, student) OVER (ORDER BY id))ENDAS'Student'FROM Seat

-- name, amt-- >= 100 units, feb 2020SELECTp.product_name,SUM(o.unit)AS unitFROM Products pLEFT JOIN Orders oONp.product_id=o.product_idWHERE DATE_FORMAT(order_date,'%Y-%m')='2020-02'GROUP BYp.product_nameHAVINGSUM(o.unit)>=100

SELECT sell_date,COUNT(DISTINCT product)AS num_sold,GROUP_CONCAT(DISTINCT product)AS'products'FROM ActivitiesGROUP BY sell_dateORDER BY sell_date

(SELECT nameAS resultsFROM Users uLEFT JOIN MovieRating mrONu.user_id=mr.user_idGROUP BY nameORDER BYCOUNT(rating)DESC, nameASCLIMIT1)UNION ALL(SELECT titleFROM Movies mLEFT JOIN MovieRating mrONm.movie_id=mr.movie_idWHERE DATE_FORMAT(created_at,'%Y-%m')='2020-02'GROUP BY titleORDER BYAVG(rating)DESC, titleASCLIMIT1 )

-- pay: last 7 days (today inclusive) - avg.amt (round, 2)SELECT visited_on, amount, ROUND(amount/7,2)AS average_amountFROM (SELECT DISTINCT visited_on,SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL6 DAY PRECEDINGAND CURRENT ROW)AS amount,MIN(visited_on) OVER() day_1FROM Customer) tWHERE visited_on>= day_1+6;

-- `union` selects only unique vals, so we use `union all` hereWITH CTEAS (SELECT requester_idAS idFROM RequestAcceptedUNION ALLSELECT accepter_idAS idFROM RequestAccepted)SELECT id,COUNT(id)AS numFROM CTEGROUP BY idORDER BY numDESCLIMIT1

SELECT    ROUND(SUM(tiv_2016),2)AS tiv_2016FROM insuranceWHERE tiv_2015IN (SELECT tiv_2015FROM insuranceGROUP BY tiv_2015HAVINGCOUNT(*)>1)AND (lat,lon)IN (SELECT lat,lonFROM insuranceGROUP BY lat,lonHAVINGCOUNT(*)=1)