SQL Interview Questions

Lin R. - "Why can not group by only on name? it gave me incorrect results when I try to do that."See full answer

Ali H. - "SQL databases are relational, NoSQL databases are non-relational. SQL databases use structured query language and have a predefined schema. NoSQL databases have dynamic schemas for unstructured data. SQL databases are vertically scalable, while NoSQL databases are horizontally scalable."See full answer

Anonymous Roadrunner - "-- Write your query here select marketing_channel, avg(purchasevalue) as avgpurchase_value, avg(case when purchasevalue > 0 then 1 else 0 end) as conversionrate from attribution group by 1 order by 3 desc `"See full answer

Aneesha K. - "-- Write your query here select u.userid as userid, IFNULL(sum(purchase_value), 0) AS LTV FROM user_sessions u JOIN attribution a ON u.sessionid = a.sessionid group by user_id order by LTV desc ; Needs a full join. Wondering why cant we do a left outer join here. All the sessions should have complete data."See full answer

Jessica C. - "select customer_id, order_date, orderid as earliestorder_id from ( select customer_id, order_date, order_id, rownumber() over (partition by customerid, orderdate order by orderdate) as orderrankper_customer from orders ) sub_table where orderrankper_customer=1 order by orderdate, customerid; Standard solution assumed that the orderid indicates which order comes in first. However this is not always the case, and sometime orderid can be random number withou"See full answer

Alexey T. - "-- The text of the task is a bit confusing. If the status is repeated several -- times, then in the end you should show as start_date the date of the first -- occurrence, and in end_date the date of the last occurrence of this status, -- and not the date of the beginning of the next status with t1 as (select order_id, status, orderdate as startdate, lead(orderdate) over (partition by orderid order by orderdate) as enddate, ifnull(lag(status) over (partition by order_id order by or"See full answer

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

Pk - "Without using the window function: SELECT w1.viewer_id FROM watch_time w1 JOIN watch_time w2 ON abs(w1.month - w2.month) = 1 AND w1.viewerid = w2.viewerid AND w1.year = w2.year GROUP BY w1.viewer_id HAVING sum(CASE WHEN w1.month > w2.month AND w1.watchhours > w2.watchhours THEN 1 ELSE 0 END) > 2 `"See full answer

Anonymous Roadrunner - "-- Write your query here select avg(julianday(dateend) - julianday(datestart)) as average_duration from campaign; `"See full answer

Alexey T. - "with t1 as (select employee_name, department_id, salary, avg(salary) over (partition by departmentid) as avgsalary, abs(salary - avg(salary) over (partition by department_id)) as diff from employees ) select employee_name, department_id, salary, avg_salary, denserank() over (partition by departmentid order by diff desc) as deviation_rank from t1 order by departmentid asc, deviationrank asc, employee_name `"See full answer

Aaron W. - "Clarification questions What is the purpose of connecting the DB? Do we expect high-volumes of traffic to hit the DB Do we have scalability or reliability concerns? Format Code -> DB Code -> Cache -> DB API -> Cache -> DB - APIs are built for a purpose and have a specified protocol (GET, POST, DELETE) to speak to the DB. APIs can also use a contract to retrieve information from a DB much faster than code. Load balanced APIs -> Cache -> DB **Aut"See full answer

Anonymous Wombat - "with cte as ( select a.departmentid departmentid , b.departmentname departmentname , extract(month from a.order_date) mm , sum(a.orderamount) currentval , lag(sum(a.order_amount)) over(partition by a.departmentid order by extract(month from a.orderdate)) previous_val from orders a inner join departments b on a.departmentid=b.departmentid where extract(year from a.order_date) = 2022 and extract(month from a.order_date) in (11,12) group by 1,2,3 ) select departmentid, departmentname, c"See full answer

Alina G. - "SELECT upsellcampaignid, COUNT(DISTINCT trans.userid) AS eligibleusers FROM campaign JOIN "transaction" AS trans ON transactiondate BETWEEN datestart AND date_end JOIN user ON trans.userid = user.userid WHERE iseligibleforupsellcampaign = 1 GROUP BY upsellcampaignid `"See full answer

Anonymous Tortoise - "SELECT i.item_category, o.order_date, SUM(o.orderquantity) AS totalunits_ordered FROM orders o JOIN items i ON o.itemid = i.itemid WHERE o.order_date >= DATE('now', '-6 days') GROUP BY i.item_category, o.order_date ORDER BY i.item_category ASC, o.order_date ASC;"See full answer

מאיה ט. - "select t.user_id, u.name, count(t.id) as orders from transactions t inner join users u on t.user_id=u.id group by 1,2 order by count(t.id) desc limit 1"See full answer

Gowtami K. - "with cte as ( select user_id, timestamp as current_login, lag(timestamp,1) over(partition by userid order by timestamp) as previouslogin , round(abs(julianday(timestamp)-julianday(lag(timestamp,1) over(partition by userid order by timestamp)))2460)as minuteselapsed from useractivitylog where activity_type ='LOGIN' ) select userid, currentlogin, previouslogin, minuteselapsed from cte where currentlogin previouslogin `"See full answer

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

Gowtami K. - "select DISTINCT p.product_id, p.product_name , CASE when sale_date is null then 'Not Sold' else 'Sold' END as sale_status from products p left join sales s on p.productid= s.productid `"See full answer

Jayveer S. - "WITH suspicious_transactions AS ( SELECT c.first_name, c.last_name, t.receipt_number, COUNT(t.receiptnumber) OVER (PARTITION BY c.customerid) AS noofoffences FROM customers c JOIN transactions t ON c.customerid = t.customerid WHERE t.receipt_number LIKE '%999%' OR t.receipt_number LIKE '%1234%' OR t.receipt_number LIKE '%XYZ%' ) SELECT first_name, last_name, receipt_number, noofoffences FROM suspicious_transactions WHERE noofoffences >= 2;"See full answer

Jayveer S. - "SELECT d.department_name, SUM(o.orderamount) AS totalrevenue FROM orders o JOIN departments d ON o.departmentid = d.departmentid WHERE o.order_date >= DATE('now', '-12 months') GROUP BY d.department_name ORDER BY total_revenue DESC; "See full answer