SQL Interview Questions

Aneesha K. - "Test case is wrong. It expects to sort in asc order of month_year. -- Write your query here SELECT strftime('%Y-%m', createdat) AS monthyear, COUNT(DISTINCT userid) AS numcustomers, COUNT(t.id) AS num_orders, SUM(price * quantity) AS order_amt FROM transactions t INNER JOIN products p ON t.product_id = p.id GROUP BY month_year ORDER BY month_year ; "See full answer

Derrick M. - "SELECT d.departmentname,SUM(o.orderamount) AS total_revenue FROM orders o JOIN departments d ON d.departmentid =o.departmentid WHERE o.orderdate >= CURRENTDATE - INTERVAL '12 months' GROUP BY d.department_name ORDER BY total_revenue DESC; `"See full answer

Lucas G. - "SELECT COUNT(*) unique_conversations FROM messenger_sends WHERE senderid < receiverid"See full answer

Alvin P. - "SELECT e1.empid AS manageremployee_id, e1.empname AS managername, COUNT(e2.empid) AS numberofdirectreports FROM employees AS e1 INNER JOIN employees AS e2 ON e2.managerid = e1.empid GROUP BY e1.emp_id HAVING COUNT(e2.emp_id) >= 2 ORDER BY numberofdirectreports DESC, managername ASC `"See full answer

Erjan G. - "this task is misleading . i used lag(1) and lead(1) cuz it did not say "compare temperature from 2 days before and 1 day before" , it reads to me as if its asking "compare cur temperature to prev and future and see if it rose and fall""See full answer

E L. - "WITH CTE AS ( SELECT *, ROWNUMBER()OVER(PARTITION BY utxoid ORDER BY transactionid) AS trxrk FROM transactions JOIN transaction_inputs USING (transaction_id) JOIN utxo USING (utxo_id) ) SELECT transaction_id AS InvalidTransactionId FROM CTE WHERE sender!=address OR trx_rk > 1 `"See full answer

Jayveer S. - "SELECT p1.player_name AS player1, p2.player_name AS player2, ABS(p1.level - p2.level) AS level_disparity FROM players p1 JOIN players p2 ON p1.playername < p2.playername WHERE ABS(p1.level - p2.level) <= 5 ORDER BY level_disparity ASC;"See full answer

Derrick M. - "SELECT COUNT(DISTINCT o.customerid) AS customers, d.departmentname FROM orders o INNER JOIN departments d ON d.departmentid = o.departmentid WHERE d.departmentname IN ('Electronics','Fashion') AND o.orderdate BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY d.department_name; `"See full answer

Gowtami K. - "with cte as (select *, row_number() over(order by score desc) as rn from players) select player_name, score, rn as ranking from cte where rn= 4 or rn =6 or rn =11 `"See full answer