SQL Interview Questions

Ravi K. - "select e.firstname as firstname, m.salary as manager_salary from employees e join employees m on e.manager_id = m.id where e.salary > m.salary; `"See full answer

E B. - "Clarifying Points Number of users: unique user id “More than three people” we have user id for the call recipient, and we want number of unique recipients called. We would not count each instance of calling someone. Last week: last 7 days from today SELECT User_id, COUNT(distinct calleduserid) as userscalledcount FROM calls WHERE calldate between currentdate - interval ‘7 day’ and current_date Group by user_id HAVING COUNT(distinct calleduserid) >= 3 This return"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

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

Temesgen B. - "Very Good Explanation Thanks For This Rely Good Explanation"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

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

Bala G. - "WITH ActiveUsersYesterday AS ( SELECT DISTINCT user_id FROM user_activity WHERE activity_date = CAST(GETDATE() - 1 AS DATE) ), VideoCallUsersYesterday AS ( SELECT DISTINCT user_id FROM video_calls WHERE call_date = CAST(GETDATE() - 1 AS DATE) ) SELECT (CAST(COUNT(DISTINCT v.userid) AS FLOAT) / NULLIF(COUNT(DISTINCT a.userid), 0)) * 100 AS percentagevideocall_users FROM ActiveUsersYesterday a LEFT JOIN VideoCallUsersYesterday v ON a.userid = v.userid;"See full answer

Anonymous Anteater - "select employeename, employeeid, salary, department, DR from ( select employeename, employeeid, salary, dense_rank() over (partition by department order by salary desc) DR, department from employee ) where DR <=3 order by department, DR"See full answer

Bala G. - "SELECT s.Sale_Date, SUM(si.Quantity * si.SalePrice) AS TotalRevenue FROM Sales s JOIN SaleItems si ON s.SaleID = si.Sale_ID GROUP BY s.Sale_Date ORDER BY s.Sale_Date; "See full answer

Rishabh L. - "with empbysalary as ( select id, firstname, lastname, salary, department_id, rank() over (partition by department_id order by salary desc) as rnk from employees ) select d.name as department_name, e.id as employee_id, e.firstname, e.lastname, e.salary from empbysalary e join departments d on e.department_id=d.id where e.rnk=1 order by 1; `"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

Chase C. - "Wording is a bit confusing on this one. This one can be interpreted as asking for count of unmatched bookings per user. By saying average here, what they really mean "what is the proportion of unmatched bookings to total bookings for each user." Important clarifying question to ask here if this was asked in a real interview!"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

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

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

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

Hayatu H. - "How do you find consecutive days for login (MySQL, SQL, date, subquery, MySQL 5.7, development)? 1 Follow Request Answer More All related (34) Recommended 📷 Trausti Thor Johannsson · Follow Been using MySQL for more than 16 yearsDec 27 There are functions like DATEDIFF but there are also BETWE"See full answer