Sample Answer
I’ll walk you through what I did for a reporting-heavy analytics feature serving about 20M rows and growing ~500k/day. Reads were 90%+ of traffic, with dashboards hitting the DB every few seconds. I started by normalizing core entities (accounts, events, dimensions) to avoid duplication, then selectively denormalized into a summary table optimized for the top three queries. For indexing, I looked at actual query patterns and `EXPLAIN` plans. We added composite indexes matching WHERE/ORDER BY clauses, like `(account_id, created_at DESC)` and partial indexes for active accounts only. That alone dropped p95 query latency from ~1.8s to ~180ms. To keep writes reasonable, I avoided over-indexing and introduced time-based partitioning on `created_at`, so older partitions became read-only. Combined with a nightly maintenance job (ANALYZE, index defrag) and read replicas for reporting, we scaled to 10x data volume over a year without noticeable slowdown for users.
Keywords
In your civil engineering studies, what specific design coursework or project work did you complete related to irrigation channels or canals (e.g., design of lined/unlined canals, distributaries, minors)? Describe one such design in detail, including how you determined discharge, permissible velocity, section dimensions, and lining choice for Gujarat-type soil and climate conditions.
Based on your hydrology and irrigation engineering background, explain how you would estimate the irrigation water requirement for a kharif crop in a semi-arid region of Gujarat. Walk me through each step: from reference evapotranspiration estimation, crop coefficient selection, effective rainfall calculation, to arriving at canal discharge for a given command area.
On your resume you mention working on a cross-functional project (e.g., involving multiple teams or stakeholders). Describe a situation from that project where priorities conflicted—how did you navigate the trade-offs and what was the final outcome?
Walk me through a recent multi-channel digital marketing campaign you managed end-to-end. How did you set objectives, choose channels, allocate budget, and measure success?
In your resume you note improving or optimizing [a process, KPI, or metric]. What specific baseline metrics did you start from, what steps did you personally take, and how did you verify that the improvement was due to your changes rather than external factors?