Question 1

Write an SQL query to find the second-highest salary from an employee table.

Accepted Answer

**Using subquery with MAX**:
```sql
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
```

**Using LIMIT/OFFSET** (MySQL, PostgreSQL):
```sql
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
```

**Using DENSE_RANK** (ANSI SQL, most robust):
```sql
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employee
) t
WHERE rk = 2;
```

**Architectural Logic & Trade-offs**:
- **Subque...

Question 2

Explain Fact and Dimension Tables with examples.

Accepted Answer

Architecture: Star schema centralizes measurable events in fact tables; dimensions provide semantic context. Why this design: Facts are append-heavy and grow unbounded; dimensions are smaller and change slowly. Separating them optimizes for different access patterns. Fact grain defines the entire schema—get it wrong and joins become wrong. Example: sales_fact (quantity, revenue, date_key, product_key, customer_key) at grain one row per transaction....

Question 3

How do you remove duplicate rows in BigQuery?

Accepted Answer

Approach: Use ROW_NUMBER() OVER (PARTITION BY dedup_keys ORDER BY tie_breaker) to define which row to keep; filter rn = 1. Preferred pattern: CREATE OR REPLACE TABLE ... AS SELECT * EXCEPT(rn) FROM (SELECT *, ROW_NUMBER() OVER (...) AS rn ...) WHERE rn = 1. Why CREATE OR REPLACE over DELETE: BigQuery is columnar; DELETE is a rewrite under the hood. For large tables, CREATE OR REPLACE is a single scan+write vs DELETE's read-modify-write....

Question 4

Difference Between Internal and External Tables in BigQuery

Accepted Answer

**Architectural Logic**: Internal (native) tables: BigQuery owns metadata and storage (Colossus). Columnar layout, partitioning, clustering, automatic optimization. Dropping = data deleted. External tables: BigQuery owns metadata only; data lives in GCS, Drive, Bigtable. Dropping = metadata deleted, data persists. **Why**: Internal enables DML, streaming inserts, partitioning strategies, cost-effective storage classes....

Question 5

Explain Common Table Expressions (CTEs) and their benefits.

Accepted Answer

**Architectural Logic**: CTEs are named subqueries in a WITH clause, evaluated as defined (or materialized, depending on engine). They provide logical decomposition without forcing physical materialization. **Why**: Readability and reuse—complex pipelines split into stages (raw → cleansed → aggregated). Recursion for hierarchies (org charts, bill-of-materials). Some engines inline CTEs; others (e.g., BigQuery) can materialize for reuse....

Question 6

Explain SQL Window Functions with examples.

Accepted Answer

**Architectural Logic**: Window functions compute over a "frame" of rows related to the current row without collapsing rows. Syntax: func() OVER (PARTITION BY ... ORDER BY ... [frame]). Categories: Ranking (ROW_NUMBER, RANK, DENSE_RANK), Aggregate (SUM, AVG over partitions), Value (LAG, LEAD, FIRST_VALUE). **Why**: Enable row-level analytics (running totals, moving averages, prior/next comparisons) without self-joins. Self-joins duplicate data and are slower....

Question 7

Explain the use of the MERGE statement in SQL.

Accepted Answer

**Architectural Logic**: MERGE (upsert) performs INSERT, UPDATE, DELETE in one atomic statement based on a join condition. Syntax: MERGE INTO target USING source ON (key) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... [WHEN NOT MATCHED BY SOURCE THEN DELETE]. **Why**: Single pass over target and source; avoids read-modify-write race conditions; efficient for SCD Type 1/2, incremental loads, CDC sync. **Scalability**: Join key should be indexed; large source scans can lock target....

Question 8

How do you handle NULL values in SQL? Mention functions like COALESCE and ISNULL.

Accepted Answer

**Architectural Logic**: NULL represents unknown/missing; it propagates through expressions (NULL + 1 = NULL). Handling: IS NULL / IS NOT NULL for predicates; COALESCE(val1, val2, ...) for first non-NULL (portable); ISNULL/IFNULL for dialect-specific default; NULLIF(val1, val2) to normalize to NULL. **Why**: JOIN on NULL yields no match (NULL != NULL). Aggregates ignore NULL except COUNT(*). Explicit handling prevents silent exclusions....

Question 9

How do you optimize a long-running SQL query?

Accepted Answer

**Architectural Logic**: Optimization is diagnostic-first. 1. Profile: EXPLAIN/EXPLAIN ANALYZE to find bottleneck (scan, join, sort, spill). 2. Reduce input: Filter early (WHERE, partition pruning); SELECT only needed columns. 3. Indexing: B-tree on filter/join columns; avoid over-indexing (writes slow). 4. Partitioning: Date/tenant partitioning for pruning. 5. Join strategy: Broadcast small dims; avoid cross joins. 6. Statistics: Up-to-date stats for planner. 7....

Question 10

How would you handle duplicate records in an SQL table?

Accepted Answer

**Architectural Logic**: 1. Identify: GROUP BY key HAVING COUNT(*) > 1. 2. Resolve: ROW_NUMBER() OVER (PARTITION BY key ORDER BY tiebreaker) then keep rn=1. 3. Prevent: UNIQUE constraint, PK; MERGE/INSERT with conflict handling. **Why**: Duplicates indicate ingestion bug, missing idempotency, or intentional multi-version (e.g., SCD2). Fix root cause before ad-hoc dedup. **Scalability**: DELETE from CTE/subquery can lock; prefer INSERT INTO new_table SELECT ......

Incedo SQL Interview Questions

Companies asking these questions

All 21 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Incedo SQL Interview Questions

Companies asking these questions

All 21 Questions

More Interview Prep Guides

Get All Answers in PDF Format