Complex Datasets with SQL (Data Scientist Mock Interview)

In this mock SQL interview, we ask an Adobe Data Scientist to pull customer and transaction data at an e-commerce company. They discuss window functions, aggregations, and indexing in SQL.

To extract key data from large datasets, use appropriate queries or functions. Window functions are useful for larger tables, allowing partitioning by various columns and sorting data. Proper indexing of tables can also improve performance.

When writing queries, consider details and structure, and comment on logic and sub-parts. Ask clarifying questions to ensure assumptions are correct.
To optimize queries that are repeatedly run over time, identify primary and foreign keys and index tables appropriately.

Chapters (Powered by ChapterMe) -
00:00 - Intro
00:42 - Query reports users, transactions, order amounts
03:31 - Monthbymonth reporting with distinct counts
04:40 - Monthly reporting query pulling roles, transactions, order amount
05:00 - Date part function SQL for 2020 year summary
05:37 - Output Monthly name, customers, orders, order amount
05:57 - Perfect query improvement tips
08:18 - Query output for users orders
09:10 - Logic for capturing users orders
11:08 - Window functions offer more scalability, flexibility
12:19 - Excellent queries, thoughtful actions comments
14:01 - Optimizations for faster queries
14:42 - Indexing speeds up SQL queries

ABOUT US:
Did you enjoy this interview question and answer? Want to land your dream career? Exponent is an online community, course, and coaching platform to help you ace your upcoming interview. Exponent has helped people land their dream careers at companies like Google, Microsoft, Amazon, and high-growth startups. Exponent is currently licensed by Stanford, Yale, UW, and others.

Our courses include interview lessons, questions, and complete answers with video walkthroughs. Access hours of real interview videos, where we analyze what went right or wrong, and our 1000+ community of expert coaches and industry professionals, to help you get your dream job and more!

#sql #sqlinterview #datascience #businessanalyst

[sem]

task 1:
may be it's better to count distinct order_id? to avoid duplicate counting of orders with 2 items

[habeebkhan]

I think the interview questions were too easy I guess