Interview Questions

Jacky T. - "SELECT order_amount FROM ( SELECT *, rank() OVER(ORDER BY order_amount desc) as ranking FROM departments d LEFT JOIN orders o ON d.departmentid = o.departmentid LEFT JOIN customers c ON o.customerid = c.customerid WHERE department_name = 'Fashion' ) where ranking = 2"See full answer

Derrick M. - "SELECT COUNT(DISTINCT o.customerid) AS customers, d.departmentname FROM orders o INNER JOIN departments d ON d.departmentid = o.departmentid WHERE d.departmentname IN ('Electronics','Fashion') AND o.orderdate BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY d.department_name; `"See full answer

Derrick M. - "SELECT d.departmentname,SUM(o.orderamount) AS total_revenue FROM orders o JOIN departments d ON d.departmentid =o.departmentid WHERE o.orderdate >= CURRENTDATE - INTERVAL '12 months' GROUP BY d.department_name ORDER BY total_revenue DESC; `"See full answer

Gabriella F. - "Table user is empy....... Problem with this problem "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

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

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

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

Maliki U. - "Here is my implementation: select marketing_channel, AVG(purchasevalue) as avgpurchase_value from attribution group by marketing_channel order by avgpurchasevalue DESC ; There is no need to copy and past the line of code for calculating the average into order by, just Alias is enough because going by the order of execution in sql, Always, order by is executed after executing select clause."See full answer

Anonymous Roadrunner - "-- Write your query here select avg(julianday(dateend) - julianday(datestart)) as average_duration from campaign; `"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

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

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

Simon O. - "Functional Requirement Ingest Book Reviews in real time User in the website needs specify title tho search books, return response is the list of reviews on the books. Non Functional Requirement: User get real-time book reviews> It nees to handle the search among 1 million of active users daily"See full answer

Dewansh Z. - "It's fair to assume a tech lead is an experienced person, especially in bigger organizations like Google. How we shape our thought process is part of our past experiences of default nature. I will first give it some time before I react/respond. I will try to understand the pattern where the tech lead is being devil's advocate. Eg. If he is being negative about PRD everytime, that means he had past experiences where the PRD was incomplete or scope was change; if is negative about testing scena"See full answer

Nidhi S. - "I am a book reader, so I use "goodreads" by amazon to track my reading. There are couple of things which can be done to increase user engagement on app. User engagement on goodread is creating profile tagging books - want to read, reading, completed marking up progress on daily reading. Engaging with friends and their feedback about books. Solutioning at each steps Creating profile - App respond time is 2-3 min, which is very slow as compared to any general app,its circling"See full answer

Shahnawaz K. - "I am going to assume, the person is 50 years old and uses a basic feature phone. Here is how it goes Local Context: When you come back from morning walk, you are your friends stop at a chai point to rest and have tea, what do you talk about ? (Assume he says everything and anything). Imagine there are no friends but you still want to talk and you want your friends and everyone to listen to your thoughts / rant. Imagine you don’t want to talk but you want only listen to what your frien"See full answer

Manas M. - "Since Experimentation is a Broad Term I would like to ask a couple of Clarifying questions What kind of Experimentation User Behavior with newly introduced Beta features AI/Image learning Based Experimentation to test out trial features Something else API based experimentation for user/Third-party experiments,with APIs for new features exposed(this has a lot of potential where devs of apps etc can integrate new experimentation features in their apps with minima"See full answer

Tanu M. - "Clarifying Questions : Am I the PM at google ? Yes, For which country Airport are we designing this? Lets assume its a new product so we will design it for the US airports and later expand to other countries. GTM : When do we need to launch this product? Do we have any time constraints ? Lets assume one year I am assuming why google wants to enter in this space is because it is an untapped market and google can revolutionize the market with some of the most emerging tehnologies by e"See full answer