After Query Optimisation, Indexing & Database Performance, the adjacent question and reply mobility is not only "Can you constitute the query?" It is "Can the business spot the number your query produces?" In Indian analytics teams astatine Flipkart, Razorpay, Zomato, CRED, and Groww, SQL believe progressively intends owning reliable, accordant metrics crossed dashboards, Python notebooks, exertion programming interfaces, and the information warehouse. This instruction shows really to move from query penning to analytics engineering utilizing dbt, metrics layers, contracts, lineage, and testing.
- SQL believe should commencement pinch array relationships, superior keys, overseas keys, and granularity because incorrect joins create incorrect business metrics.
- SQL constitute bid is not SQL execution order: FROM and JOIN tally first, past WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT aliases TOP.
- Real analytics believe includes moving totals, rankings, cohort retention, year-over-year growth, funnels, RFM segmentation, churn detection, and NULL handling.
- Analytics engineering is the displacement from advertisement hoc queries to governed information models wherever gross intends the aforesaid point successful Tableau, Python, and the information warehouse.
- dbt, short for information build tool, supports SQL-based translator pinch type control, testing, documentation, and lineage.
- A metrics furniture centralises definitions truthful each instrumentality consumes the aforesaid look alternatively of each expert recreating it differently.
- The strongest question and reply answers harvester SQL logic pinch information contracts, information lineage, and automated tests that forestall silent metric breakage.
Big Picture - SQL Practice arsenic Metric Ownership
Think of SQL believe arsenic a layered system. At the base, you request relational modelling and correct SQL. Above that, you request analytics patterns for business questions. At the top, analytics engineering makes those numbers consistent, testable, and reusable crossed the organisation.
A reliable metric reply follows this structure: specify the business metric, place the atom of the data, constitute the SQL logic, validate separator cases specified arsenic NULLs and duplicates, people the metric successful a cardinal layer, and show it pinch tests and lineage.
In Flipkart, Razorpay, and Zomato, the information expert domiciled has evolved into analytics engineering. The anticipation is not conscionable querying data, but ensuring that a metric specified arsenic gross intends the aforesaid point successful Tableau, Python, and the information warehouse. The strategical truthful what: SQL accomplishment is now judged by whether your metric tin beryllium trusted repeatedly, not conscionable whether your query runs once.
Start pinch the Relational Model and Granularity
A relational database stores information successful system tables pinch rows, called records, and columns, called attributes. A primary key uniquely identifies each row, specified arsenic customer_id. A foreign key links 1 array to different table's superior key, specified arsenic orders.customer_id linking to customers.customer_id.
This matters because astir SQL errors successful interviews are not syntax errors. They are atom errors. Before a GROUP BY, ask: One statement represents 1 what? If you group orders astatine customer level but subordinate to a merchandise array astatine point level, you whitethorn accidentally multiply gross and past hide the problem pinch DISTINCT.
The nuance is that normalised creation and analytics creation optimise for different goals. Online Transactional Processing, aliases OLTP, records day-to-day business transactions and is write-heavy. Online Analytical Processing, aliases OLAP, analyses humanities information and is read-heavy pinch analyzable aggregations.
Choose the Right Analytics Schema
A star schema has a cardinal truth table, specified arsenic Sales, Orders, aliases Events, connected to magnitude tables specified arsenic Customer, Product, Time, Location, and Promotion. It is elemental and accelerated for Business Intelligence, aliases BI, which intends reporting and dashboarding for business users. A snowflake schema further normalises magnitude tables into sub-dimensions, redeeming retention but requiring much joins.
The norm of thumb from the root is direct: usage prima schema for analytics and BI erstwhile query capacity matters much than storage. Use snowflake schema erstwhile retention is simply a interest aliases dimensions are very large.
Respect SQL Execution Order Before Writing Fancy Queries
SQL, aliases Structured Query Language, is written successful 1 bid but executed successful another. This is simply a favourite question and reply trap because candidates often effort to usage a SELECT othername successful WHERE, aliases select a model usability othername straight successful the aforesaid query. The root highlights that SQL execution starts pinch FROM and JOIN, not SELECT.
The applicable instruction is simple: constitute the query arsenic if the database will build a moving array first, trim rows next, group aft that, and only past show columns. For ranking problems, specified arsenic apical products by gross wrong each category, usage a Common Table Expression, aliases CTE, erstwhile you request to select connected a model usability result.
Practice Analytics Patterns connected Indian Business Scenarios
Once the relational exemplary is clear, SQL believe should move to repeatable business patterns. The root examples are useful because they lucifer existent analytics questions: Flipkart regular sales, Razorpay checkout conversion, Indian EdTech retention, Nykaa quarterly growth, and Paytm wallet churn. The constituent is not memorising query syntax; it is recognising the business shape and choosing the correct SQL construct.
Two nuances matter successful interviews. First, COUNT(*) counts each rows including NULLs, while COUNT(column) skips NULL values. Second, AVG ignores NULLs, truthful you should explicitly determine whether to COALESCE missing values earlier aggregating.
Move from SQL Queries to Analytics Engineering
Analytics engineering is the subject of turning earthy information into tested, documented, reusable analytics-ready tables. In the source, the reality cheque is clear: the information expert domiciled astatine Flipkart, Razorpay, and Zomato has evolved. Interviewers now inquire really you guarantee metric consistency crossed tools.
dbt, short for information build tool, is simply a SQL-based translator instrumentality pinch type control, testing, documentation, and lineage. A metrics layer is simply a centralised azygous root of truth for metric definitions consumed by BI devices aliases code. Data contracts are schema agreements betwixt engineering teams that nutrient information and analytics teams that devour it. Data lineage is the ocular representation of information travel from root to toggle shape to dashboard. Data testing checks information value automatically, including not-null, uniqueness, and referential integrity.
The nuance is that ownership whitethorn overlap. Engineering whitethorn ain arena production, analytics engineering whitethorn ain transformations and tests, and analysts whitethorn ain business interpretation. A beardown campaigner explains really these layers activity together alternatively of claiming that 1 squad ever owns everything.
Worked Example - Razorpay Checkout Funnel Metric
Consider a Razorpay checkout travel wherever business teams want to cognize stage-level conversion. A basal SQL reply tin count users astatine each stage, but a metric ownership reply must besides guarantee that each dashboard and notebook calculates the chimney the aforesaid way.
This is the quality betwixt saying "I tin constitute a chimney query" and saying "I tin make the chimney metric trustworthy." The 2nd reply is stronger because it recognises that business metrics unrecorded beyond a azygous SQL editor tab.
A Practical Framework for SQL Practice pinch Indian Datasets
Use this five-step process erstwhile practising connected datasets inspired by Flipkart orders, Razorpay checkout events, Paytm transactions, Nykaa revenue, aliases Indian EdTech personification activity. The examples travel from the root patterns, but the model is reusable crossed analytics interviews.
SQL Red Flags That Weaken Metric Trust
The root lists respective SQL traps that costs question and reply rounds because they nutrient numbers that look plausible but are wrong. The biggest shape is utilizing SQL syntax to hide a modelling problem. DISTINCT whitethorn region copy rows visually, but it does not explicate why the copy rows appeared.
Structuring a SQL Practice pinch Real Indian Datasets Interview Answer
"You person to specify gross for a Flipkart-style analytics dashboard. How would you guarantee the number is accordant crossed SQL, Tableau, Python, and the warehouse?"
The number 1 measurement candidates get this incorrect is by jumping consecutive into SELECT statements. Start pinch the business meaning and grain, past constitute the query, past explicate really the metric will enactment accordant aft it leaves your laptop.
Conclusion
SQL believe pinch existent Indian datasets is nary longer conscionable astir producing a consequence set. The halfway accomplishment is building business metrics that are correctly joined, consistently defined, tested, documented, and traceable from dashboard backmost to earthy data.
The astir predominant correction is treating SQL arsenic a one-time query workout alternatively of a metric ownership problem. It costs points because interviewers tin spot that the reply whitethorn activity successful 1 notebook, but neglect erstwhile the aforesaid metric is reused crossed Tableau, Python, APIs, and the warehouse.
English (US) · 
Indonesian (ID) ·