Data Analyst SQL Interview Questions

Mohit C. - "-- LTV = Sum of all purchases made by that user -- order the results by desc on LTV select u.user_id, sum(a.purchase_value) as LTV from user_sessions u join attribution a on u.sessionid = a.sessionid group by u.user_id order by sum(a.purchase_value) desc"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

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

Michelle M. - "WITH cte AS ( SELECT customer_id, COUNT(*) AS noofoffences FROM transactions WHERE receipt_number like '%999%' OR receipt_number like '%1234%' OR receipt_number like '%XYZ%' GROUP BY 1 HAVING COUNT(*) >=2 ) SELECT firstname, lastname, receipt_number, noofoffences FROM cte INNER JOIN customers using(customer_id) INNER JOIN transactions using(customer_id) ORDER BY 1,2,3,4 `"See full answer

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