Question 1

How would you read data from a web API? What steps would you follow after reading the data?

Accepted Answer

**Why this matters**: APIs are external dependencies—unreliable, rate-limited, schema-evolving. Production ingestion must be resilient, auditable, and idempotent. **Steps**: (1) **Contract & auth**: Define schema (Pydantic, JSON Schema); store credentials in secrets manager (Vault, AWS Secrets). OAuth tokens need refresh logic. (2) **Pagination & rate limiting**: Offset/cursor-based; loop until empty. Respect Retry-After; implement exponential backoff....

Question 2

What is the difference between SQL and NoSQL databases?

Accepted Answer

**Why both exist**: SQL excels at structured data, complex joins, and strong consistency. NoSQL excels at unstructured/semi-structured data, horizontal scale, and flexible schema. **SQL (relational)**: Fixed schema, ACID, vertical scale (or managed horizontal via Citus, etc.). Optimized for joins and aggregations. Best for: transactional systems, reporting, anything requiring referential integrity. **NoSQL**: Schema-flexible, BASE (eventual consistency), horizontal scale....

Question 3

APPLY Operator - CROSS APPLY and OUTER APPLY

Accepted Answer

**CROSS APPLY**: Invokes table-valued function/subquery per row; like INNER JOIN—excludes rows where function returns nothing. **OUTER APPLY**: Like LEFT JOIN—keeps all left rows, NULL where function returns nothing. **Use**: Correlated subqueries, JSON parsing, string splitting. **PostgreSQL**: LATERAL JOIN (CROSS JOIN LATERAL, LEFT JOIN LATERAL). **Performance**: Can be expensive—row-by-row. Prefer set-based when possible....

Question 4

An existing job running longer suddenly: how to analyze the issue?

Accepted Answer

**STAR approach**: **Situation**: Job ran 2 hr, now 6 hr. **Task**: Find root cause. **Analysis**: (1) Data volume growth—compare input size. (2) Skew—Spark UI shows uneven partitions. (3) Resource contention—other jobs. (4) Source throttling—DB or API limits. (5) Partition pruning—missing partitions? **Actions**: Add partitions; salting for skew; increase resources; fix source limits. **Result**: Document in runbook; add monitoring....

Question 5

Calculate a 7-day moving average of clicks for each user_id

Accepted Answer

**SQL**: SELECT user_id, date, clicks, AVG(clicks) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM clicks. **Spark**: Window.partitionBy('user_id').orderBy('date').rowsBetween(-6, 0). **Why ROWS**: ROWS 6 PRECEDING = 7 rows (current + 6). Use RANGE for gaps....

Question 6

Calculate a 7-day moving average of orders for each city in the Swiggy database.

Accepted Answer

**SQL**: SELECT city_id, order_date, order_count, AVG(order_count) OVER (PARTITION BY city_id ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM daily_orders. **Spark**: Window.partitionBy('city_id').orderBy('order_date').rowsBetween(-6, 0)....

Question 7

Calculate cumulative sales for each product in each store, ordered by sale_date

Accepted Answer

**SQL**: SELECT product_id, store_id, sale_date, amount, SUM(amount) OVER (PARTITION BY product_id, store_id ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS cumulative_sales FROM sales. **Spark**: Window.partitionBy('product_id','store_id').orderBy('sale_date').rowsBetween(Window.unboundedPreceding, Window.currentRow). **Why**: Running total for YTD, progress....

Question 8

Calculate the total number of transactions (units sold) for each product.

Accepted Answer

**SQL**: SELECT product_id, SUM(units_sold) AS total_units FROM transactions GROUP BY product_id. **With product name**: JOIN products. **Spark**: df.groupBy('product_id').agg(F.sum('units_sold').alias('total_units'))....

Question 9

Calculate the total sales amount for customers born between 1998-01-15 and 2000-01-15.

Accepted Answer

**SQL**: SELECT SUM(s.amount) AS total_sales FROM sales s JOIN customers c ON s.customer_id = c.id WHERE c.birth_date BETWEEN '1998-01-15' AND '2000-01-15'. **Note**: BETWEEN inclusive. **Spark**: sales.join(customers, 'customer_id').filter((col('birth_date') >= '1998-01-15') & (col('birth_date') <= '2000-01-15')).agg(F.sum('amount'))....

Question 10

Compute the moving average of daily transactions over a 7-day window.

Accepted Answer

**SQL**: SELECT trans_date, SUM(amount) AS daily, AVG(SUM(amount)) OVER (ORDER BY trans_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM transactions GROUP BY trans_date. **Logic**: Inner SUM for daily total; outer AVG over 7 rows. **Spark**: Group by date, then window....

Medium General Interview Questions

Companies asking these questions

All 43 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Medium General Interview Questions

Companies asking these questions

All 43 Questions

More Interview Prep Guides

Get All Answers in PDF Format