SQL Interview Questions

Harshi B. - "SELECT DISTINCT title, ROUND(AVG(rating) over (partition by title),1) avg_rating, ROUND(AVG(rating) over (partition by genre),1) genre_rating FROM rating r JOIN movie m ON r.movieid=m.movieid ORDER by 1"See full answer

Steven S. - "too many questions for clarification on this to start"See full answer

Lucas G. - "SELECT COUNT(*) unique_conversations FROM messenger_sends WHERE senderid < receiverid"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

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

מאיה ט. - "with ranking_table as ( select player_name, score, rank() over (order by score desc) as ranking from players ) select player_name, score, ranking from ranking_table where ranking in (4,6,11)"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

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

Anonymous Roadrunner - "-- Write your query here select count(distinct o.customer_id) as customers, d.department_name from orders o join departments d using (department_id) where extract(year from o.order_date) = 2022 and d.department_name in ('Electronics', 'Fashion') group by 2; `"See full answer

Gabriella F. - "Table user is empy....... Problem with this problem "See full answer