SQL Interview Questions

Yashasvi V. - "WITH RECURSIVE fibonacci_series AS ( SELECT 1 AS n, 0 AS fib1, 1 AS fib2 UNION ALL SELECT n + 1 AS n, fib2 AS fib1, fib1 + fib2 AS fib2 FROM fibonacci_series WHERE n < 20 -- Limit the series to 20 numbers ) SELECT n, fib1 AS fib FROM fibonacci_series ORDER BY n; `"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

Erjan G. - "1) select avg(session) from table where session> 180 2) select round(sessiontime/300)*300 as sessionbin, count() as sessioncount from table group by round(sessiontime/300)300 order by session_bin 3) SELECT t1.country AS country_a, t2.country AS country_b FROM ( SELECT country, COUNT(*) AS session_count FROM yourtablename GROUP BY country ) AS t1 JOIN ( SELECT country, COUNT(*) AS session_count FROM yourtablename `GROUP BY countr"See full answer

Abbas M. - "`select employeeid, employeename, sum(maxscore) totalscore from ( select e.id employeeid, e.name employeename, tr.testid , max(tr.score) maxscore from employees e join test_results tr on e.id = tr.employee_id group by tr.employeeid, tr.testid order by 1 ) group by 1,2 order by 3 desc` `"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

Anonymous Possum - "The unique id is not clear in this question"See full answer

Rafia M. - "SQL is structured query language."See full answer

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

Jaime A. - "I might be missing something but the solution, seems to be incorrect. ... , post_pairings AS ( SELECT ps.user_id, ps.postseqid AS failpostid, ps.postseqid + 1 AS nextpostid FROM post_seq AS ps WHERE ps.issuccessfulpost IS TRUE ) -- here ps.issuccessfulpost IS TRUE the condition should be FALSE -- in that way ps.postseqid is the actual failed post(failpostid) -- Additionally, at the end the join is assumming that the sequence id is going to match the post_id, wh"See full answer

Alina G. - "-- Write your query here WITH high_value AS( SELECT user_id FROM user_sessions JOIN attribution ON usersessions.sessionid = attribution.session_id GROUP BY user_id HAVING SUM(purchase_value) > 100 ORDER BY SUM(purchase_value) DESC ) SELECT usersessions.userid, marketing_channel FROM user_sessions JOIN high_value ON usersessions.userid = highvalue.userid JOIN attribution ON usersessions.sessionid = attribution.session_id GROUP BY usersessions.userid H"See full answer

Bradley E. - "I'm pretty sure Exponent's answer is wrong. In the snippet below, they use "pl.name = 'Telephones' to attempt to filter down to the Telephone transactions, but they do this within a LEFT JOIN which means all product_lines rows are returned. > LEFT JOIN product_lines pl > ON p.productlineid = pl.id > AND pl.name = 'Telephones' Below is my solution. Also, I didn't see anywhere that said the "amount" column was in cents instead of dollars, but I still divided by 100 to be consistent with Exp"See full answer

Bhavna S. - " with youngsuccrate as( select strftime('%m', postdate) AS postmonth, round(sum(issuccessfulpost)*1.0/count(issuccessfulpost),2)as yascrate from post where userid in (select userid from post_user where age between 0 and 18) group by post_month ), nonyoungsucc_rate as( select strftime('%m', postdate) AS postmonth, round(sum(issuccessfulpost)*1.0/count(issuccessfulpost),2)as nonyasc_rate from post where user_id in (select"See full answer

Meenakshi D. - "BETWEEN and HAVING clauses in SQL serve different purposes: 1. BETWEEN Clause Used to filter rows based on a range of values. Works with numeric, date, or text values. Can be used with WHERE or HAVING clauses. The range includes both lower and upper bounds. Example: Filtering employees with salaries between 30,000 and 50,000 `SELECT * FROM Employees WHERE salary BETWEEN 30000 AND 50000;` 2. HAVING Clause Used to filter **groups"See full answer

Marcos G. - "with my_table as (select * , rownumber() over(order by customerid) as row_index from customers) select customer_id, customer_name from my_table where row_index % 3 = 0"See full answer

Richard B. - "Select interface, Count(case when issuccessfulpost then 1 end) as post_success, Count() as postattempt, ROUND((COUNT(CASE WHEN issuccessfulpost THEN 1 END) * 100 / COUNT()), 2) AS postsuccess_rate from post where interface like 'Iphone%' group by 1 order by postsuccessrate desc `"See full answer

Alvin P. - "WITH high_score AS( SELECT player_id, MAX(gamescore) AS maxscore FROM scores GROUP BY player_id ), rankings AS( SELECT p.player_name, p.team_id, max_score, DENSERANK() OVER(PARTITION BY p.teamid ORDER BY h.maxscore DESC) AS scorerank FROM high_score AS h JOIN players AS p USING(player_id) ) SELECT team_id, player_name, max_score FROM rankings WHERE score_rank <= 2 GROUP BY teamid, playername O"See full answer

Lucas G. - "select sub.name subreddit_name, count(distinct us.userid) totalusers from user_subreddit as us left join subreddit as sub on us.subredditid = sub.subredditid group by us.subreddit_id having count(distinct us.user_id) > 3"See full answer