SQL Interview Questions

Tiffany A. - "SELECT employees.first_name, managers.salary AS manager_salary FROM employees LEFT JOIN employees AS managers ON employees.manager_id = managers.id WHERE employees.salary > managers.salary `"See full answer

Salome L. - "SELECT MIN(id) AS id, TRIM(LOWER(email)) AS cleaned_email FROM users GROUP BY cleaned_email ORDER BY id `"See full answer

Tiffany A. - "SELECT customer_id, order_date, orderid AS secondearliestorderid FROM ( SELECT order_id, customer_id, order_date, ROWNUMBER() OVER (PARTITION BY customerid, orderdate ORDER BY orderid ASC) AS rank FROM orders ) WHERE rank = 2 ORDER BY orderdate, customerid `"See full answer

Anonymous Roadrunner - "-- Write your query here select id, (case when p_id is null then 'Root' when pid in (select id from treenode_table) and id in (select pid from treenode_table) then 'Inner' else 'Leaf' end) as node_types from treenodetable order by 1; `"See full answer

David I. - "WITH filtered_posts AS ( SELECT p.user_id, p.issuccessfulpost FROM post p WHERE p.postdate >= '2023-11-01' AND p.postdate < '2023-12-01' ), post_summary AS ( SELECT pu.user_type, COUNT(*) AS post_attempt, SUM(CASE WHEN fp.issuccessfulpost = 1 THEN 1 ELSE 0 END) AS post_success FROM filtered_posts fp JOIN postuser pu ON fp.userid = pu.user_id GROUP BY pu.user_type ) SELECT user_type, post_success, post_attempt, CAST(postsuccess AS FLOAT) / postattempt AS postsuccessrate FROM po"See full answer

Bradley E. - "Here's a simpler solution: select u.username , count(p.postid) as countposts from posts as p join users as u on p.userid = u.userid where p.likes >= 100 group by 1 order by 2 desc, 1 asc limit 3 `"See full answer

Vysali K. - "Limit and rank() only works if there are no 2 employees with same salary ( which is okay for this use case) For the query to pass all the test results, we need to use dense_rank with ranked_employees as ( select id, firstname, lastname, salary, denserank() over(order by salary desc) as salaryrank from employees ) select id, firstname, lastname, salary from ranked_employees where salary_rank <= 3 `"See full answer

Anisha S. - "SELECT d.name as departmentname,e.id as employeeid,e.firstname,e.lastname,MAX(e.salary) as salary FROM employees e LEFT JOIN departments d ON e.department_id=d.id GROUP BY department_name ORDER BY department_name;"See full answer

Akshay D. - "SELECT u.user_id, u.user_name, u.email, ROUND(AVG(CASE WHEN b.status = 'Unmatched' THEN 1.0 ELSE 0 END), 2) AS avgunmatchedbookings FROM users u LEFT JOIN bookings b ON u.userid = b.userid GROUP BY u.user_id, u.user_name, u.email; `"See full answer

Gloriose H. - "Required output in the solution not the one requested from the question. only customerid, firstname, last_name and years were required. Please this needs to be very clear. Otherwise my answer is with totalorderyear as ( SELECT o.customer_id, c.first_name, c.last_name, EXTRACT(YEAR FROM o.orderdate) AS orderyear, COUNT(o.orderid) AS totalorders FROM orders o LEFT JOIN customers c ON c.customerid = o.customerid GROUP BY o.customerid, c.firstname, c.last"See full answer

Rudra pratap S. - "select sum(orderquantity) as totalunitsorderedyesterday from orders as ord join items as it on ord.itemid=it.itemid where order_date="2023-10-14""See full answer

Victor N. - "--country names are UPPERCASE but the table in the in the question showing lowercase. That's why it took me a while to figure it out until I ran the country column WITH RECURSIVE Hierarchy AS ( SELECT e.Emp_ID, CONCAT(e.FirstName, ' ', e.MiddleName, ' ', e.LastName) AS FullName, e.Manager_ID, 0 AS Level, CASE WHEN e.Country = 'IRELAND' THEN s.Salary * 1.09 WHEN e.Country = 'INDIA' THEN s.Salary * 0.012 ELSE s.Salary "See full answer

Evan R. - "The user table no longer exists as expected - I get an error that user does not contain user_id. Note that querying the table results in only user:swuoevkivrjfta select * FROM user `"See full answer

Daniel C. - "select name, stock from products p left join transactions t on p.id = t.product_id order by date desc limit 1"See full answer

Peter W. - "In the question it says: "above the overall average total posts", which to me implying a >, yet in the solution it uses >= Caused me 1 hr to find out. plz fix"See full answer

Alvin P. - "WITH hs AS ( SELECT test_id, employee_id, MAX(score) AS high_Score FROM test_results GROUP BY employeeid, testid ) SELECT e.id AS employee_id, e.name AS employee_name, SUM(hs.highscore) AS totalscore FROM employees AS e LEFT JOIN hs ON e.id = hs.employee_id GROUP BY e.id ORDER BY total_score DESC, e.id ASC `"See full answer

Anonymous Roadrunner - "-- Write your query here select p.id, p.title, p.budget, count(e.id) as num_employees, sum(e.salary) as total_salaries from projects p join employeesprojects ep on p.id = ep.projectid join employees e on ep.employee_id = e.id group by 1 order by 5 desc; `"See full answer

Salome L. - "SELECT items.item_category, SUM(orders.orderquantity) AS totalunitsorderedlast7days FROM orders JOIN items ON orders.itemid = items.itemid WHERE orders.order_date BETWEEN DATE('now', '-6 days') AND DATE('now') GROUP BY items.item_category `"See full answer

Salome L. - "WITH discount AS ( SELECT name, type, CASE WHEN type = 'Electronic' THEN price * 0.90 WHEN type = 'Clothing' THEN price * 0.80 WHEN type = 'Grocery' THEN price * 0.95 WHEN type = 'Book' THEN price * 0.85 ELSE price END AS discounted_price FROM products ) SELECT name, type, ROUND(discountedprice, 2) AS discountedprice FROM discount; `"See full answer

Alina G. - "Order the result in descending month is not applied in the solution"See full answer