Data Science Interview Questions Section 3: SQL, Data Warehousing, and General Analytics Concepts

by

Welcome to Section 3 of our Data Science Interview Questions series! In this part, we focus on key topics like SQL, Data Warehousing, and general analytics concepts — all critical areas for data science interviews and real-world projects. These 25 carefully selected questions will strengthen your understanding of database management, data storage strategies, and analytical thinking. Whether you’re revising for an upcoming interview or simply sharpening your expertise, this section will help you master important concepts with clarity. Let’s get started!

Also Read: Data Science Interview Questions Section 2: 25 Questions Designed To Deepen Your Understanding 

Q. Which SQL clause is used to filter grouped results?

  • WHERE
  • GROUP BY
  • HAVING ✅
  • ORDER BY

Answer: HAVING

Q. Which of these is a window function?

  • COUNT(*)
  • SUM(Amount) OVER (PARTITION BY Region) ✅
  • GROUP_CONCAT()
  • D. ROUND()

Answer: HAVING

Q. A fact table in a star schema typically contains:

  • Only dimension keys
  • Only textual fields
  • Measures and foreign keys ✅
  • Primary keys only

Answer: HAVING

Q. In Snowflake, which layer stores the actual business logic and KPIs?

  • Raw Zone
  • Staging Layer
  • Business Layer ✅
  • Reporting Layer

Answer: HAVING

Q. How do you prevent duplicate rows in SQL results?

  • WHERE NOT
  • DISTINCT ✅
  • LIMIT
  • COUNT()

Answer: HAVING

Q. In Power BI, which language is used for calculated columns and measures?

  • SQL
  • M
  • DAX ✅
  • R

Answer: HAVING

Q. Which function retrieves the previous row value in SQL?

  • FIRST()
  • LAG() ✅
  • RANK()
  • OVER()

Answer: HAVING

Q. Which clause limits the number of records returned in SQL?

  • LIMIT ✅
  • GROUP BY
  • RANK
  • JOIN

Answer: HAVING

Q. What does denormalization aim to do?

  • Increase redundancy ✅
  • Normalize data
  • Reduce storage
  • Remove joins

Answer: HAVING

Q. What is the primary purpose of indexing in databases?

  • Increase table size
  • Improve query performance ✅
  • Prevent joins
  • Reduce primary keys

Answer: HAVING

Q. Which SQL join returns all records when there is a match in either table?

  • INNER JOIN
  • LEFT JOIN
  • FULL OUTER JOIN ✅
  • CROSS JOIN

Answer: HAVING

Q. To rank rows within a partition in SQL, you use:

  • GROUP BY
  • COUNT()
  • RANK() OVER (PARTITION BY …) ✅
  • MAX()

Answer: SUM(Amount) OVER (PARTITION BY Region)

Q. Which file format is columnar and optimized for big data storage?

  • CSV
  • JSON
  • Parquet ✅
  • TXT

Answer: Parquet

Q. Which command removes duplicates from a SQL result set?

  • GROUP BY
  • DISTINCT ✅
  • ORDER BY
  • UNION ALL

Answer: DISTINCT

Q. Which Snowflake feature helps to store previous versions of data?

  • Stages
  • Time Travel ✅
  • Clustering
  • Replication

Answer: Time Travel

Q. Which SQL keyword is used for conditional logic in SELECT statements?

  • CASE ✅
  • WHERE
  • IN
  • IF

Answer: CASE

Q. What type of join returns all combinations of records from both tables?

  • INNER JOIN
  • FULL JOIN
  • CROSS JOIN ✅
  • LEFT JOIN

Answer: CROSS JOIN

Q. Which keyword combines results of two SELECT queries including duplicates?

  • UNION
  • UNION ALL ✅
  • INTERSECT
  • JOIN

Answer: UNION ALL

Q. What is the purpose of the CTE (Common Table Expression)?

  • To store data
  • To define temporary views for query readability ✅
  • To create permanent tables
  • To define indexes

Answer: To define temporary views for query readability

Q. Which type of index allows fast search on multiple columns in SQL?

  • Fulltext Index
  • Compound Index ✅
  • Clustered Index
  • Unique Index

Answer: Compound Index

Q. Which is the most optimized way to get the 2nd highest salary from a table?

  • ORDER BY salary LIMIT 2
  • SELECT MAX(salary) WHERE salary < (SELECT MAX(salary)) ✅
  • COUNT(*) – 1
  • DISTINCT salary

Answer: SELECT MAX(salary) WHERE salary < (SELECT MAX(salary))

Q. Which clause is used to rename a column in SQL output?

  • MODIFY
  • AS ✅
  • CHANGE
  • SELECT INTO

Answer: AS

Q. Which Power BI tool transforms and cleanses data before loading?

  • Model View
  • Data View
  • Power Query Editor ✅
  • Report View

Answer: Power Query Editor

Q. In Tableau, how do you define a hierarchy (e.g., Country > State > City)?

  • Calculated field
  • Drag one field over another in the data pane ✅
  • Using filters
  • Groups

Answer: Drag one field over another in the data pane

Q. Which analytical function returns the rank of a value in a dataset without gaps?

  • RANK()
  • NTILE()
  • DENSE_RANK() ✅
  • ROW_NUMBER()

Answer: DENSE_RANK()

We hope this article was helpful for you and you learned a lot about data science from it. If you have friends or family members who would find it helpful, please share it to them or on social media.

Join our social media for more.

Join us on Telegram
Follow our WhatsApp Channel

Data Science Interview Questions Data Science Interview Questions Data Science Interview Questions Data Science Interview Questions Data Science Interview Questions Data Science Interview Questions Data Science Interview Questions Data Science Interview Questions

Also Read

Spread the love