Understand first
New to this knowledge point
Complete the prerequisite concepts, read the principles and counterexamples, and then explain why in your own words.
Start with core principles →Knowledge catalogChoose core direction and segmented content
Understand → Implement → Debug → Design
Definitions, granularity, and execution boundaries in natural-language queries
Examine the semantic layer, controlled SQL, read-only execution, and result review.
Knowledge content check2026-10-03 · Check the source of the original question2026-10-02
It is recommended to understand first:
Tool calls: structure, authorization, and business contracts →From document ingestion to cited RAG answers →Which step do you want to learn from this knowledge point?
Select the starting point based on the current basis, or you can go deeper one by one. When you encounter an unfamiliar concept, go back to the core principles first; use the knowledge exercises to check your understanding when you are finished.
Realize again
Prepare to write the principles into code
Understand implementation steps and boundaries, complete small tasks, and check results against acceptance requirements.
View the code example →Will troubleshoot
Need to handle failures and changes in conditions
Follow the continuous questioning to locate the failure premise, and then compare the migration cases to explain how the plan should be adjusted.
Continue to delve deeper into the problem →Able to choose
Need to design or review plans
Combine engineering deductions and senior self-evaluation standards to explain the applicable conditions, costs and alternatives of the plan.
Analyze engineering scenarios →Knowledge unit directory
LEARN · PRACTICE · REFLECT
Knowledge learning and personal records
First read along the principles, Q&A and migration cases. When you need to check your understanding, switch to reinforcement exercises or start personal recording.
Answers and personal notes
Can be practiced directly. After logging in, answers, favorites, and notes will be saved to your account.
Log in and saveEach modified commit will be kept as an independent history. Your level of mastery is up to you to evaluate yourself against the standards.
Core concept · Definitions, granularity, and execution boundaries in natural-language queries
Understand the core principles first
Preparatory concepts:SQL JOIN and aggregation, Database permissions, Metrics and Entity Modeling
SQL runnability only proves that the syntax and execution are true; the correct answer also requires that the entity, granularity, time and permissions match the user's intent. Security constraints and business semantics must be verified separately.
Define the calculation before generating SQL
“Today’s China Merchants Bank balance” needs account scope, balance type, time, and currency. Capture structured intent, then map it to governed tables or views. Models suggest SQL; trusted identity supplies tenant and row scope without allowing wider access.
Valid SQL can still be unsafe
SELECT-only policies do not establish harmlessness or bounded cost. Functions may have effects and queries may be expensive. Combine least-privilege roles, function/table allowlists, read-only transactions, and timeouts. Parameterization prevents value injection without replacing structural or access checks. Verify actual PostgreSQL roles, including owner, superuser, and BYPASSRLS boundaries.
Aggregation depends on grain
One balance per account and time joined to many transactions can multiply SUM(balance), despite valid syntax and permissions. Establish keys and grain before joining; aggregate at compatible grain or use existence filtering. Verify by entity keys rather than plausible-looking totals.
Return an explainable result
Report account scope, balance type, currency, and cutoff. Multiple queries may require a consistent snapshot. Calculate money with fixed precision and deterministic code; models explain definitions. Clarify ambiguity: silently replacing “Chongqing branch” with “accounts opened in Chongqing” changes the question even if SQL is valid.
Check understanding with a question
When searching a database using natural language, how to prevent the SQL from being checked in the wrong definition or exceeding authority even if it is legal?
Map the question to entities, metrics, time, currency, and authorized scope before generating restricted SQL. AST validation and parameterization need least-privilege roles, allowed tables/functions, row policies, timeouts, and resource limits. Return query definitions and snapshots; verify money deterministically. Clarify account scope and balance time rather than merely proving the query runs.
Realization and trade-offs
Solve the business semantics first
Define the definition, status conditions and units for indicators such as "expenditure", "available balance" and "interest spread". The bank abbreviation is mapped to the controlled entity dictionary, and regions and branches should not be matched solely by string inclusion. The time range is explicitly converted into the start and end time of the business time zone, and the use of occurrence date, recording date or value date is confirmed. Clarify first when there are no conditions that determine the outcome, or clearly demonstrate the default approach.
Execution boundaries are established both outside and inside the database.
Parse SQL AST, restricting statement types, tables, columns, and functions, using bound parameters for values, and identifiers from allowed lists. Multiple statements and uncontrolled external access are prohibited; read-only queries may also produce dangerous behaviors or consume a lot of resources through functions, so database account, network and function permissions still need to be tightened. Setting statement timeouts, number of result rows, and query cost constraints, and execution plan checks cannot replace run limits.
Tenant isolation cannot rely on prompts
The trusted identity determines the tenant and authorization scope, and the model cannot freely specify tenant_id. You can use controlled views, gateway injection conditions, and database row-level security to create multiple layers of constraints and verify that high-privilege roles bypass these policies. The cache contains the identity scope, data version, and definition, and cannot allow another user to hit a higher privilege result.
How can the answer be reviewed?
Returns indicator definition, time range, currency, data time and query summary; authorized auditors can view the actual query and parameters. Cross-checking aggregations with known samples and independent SQL, specifically testing for duplicate accumulations, refund offsets, and nulls caused by one-to-many JOINs. Empty results should be distinguished from query failure. The interviewer can actively discover JOIN amplification, which is more valuable than just writing SELECT.
code example
How to add up the amount repeatedly in one-to-many JOIN
Use integers to demonstrate repeated accumulation; complete query also requires tenant permissions, currency, time and transaction definition.
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE payments(id INTEGER PRIMARY KEY, amount_cents INTEGER);
CREATE TABLE tags(payment_id INTEGER, tag TEXT);
INSERT INTO payments VALUES (1, 10000), (2, 20000);
INSERT INTO tags VALUES (1, 'bank'), (1, 'expense'), (2, 'expense');
""")
wrong = db.execute("SELECT SUM(p.amount_cents) FROM payments p JOIN tags t ON p.id=t.payment_id").fetchone()[0]
correct = db.execute("SELECT SUM(p.amount_cents) FROM payments p WHERE EXISTS (SELECT 1 FROM tags t WHERE t.payment_id=p.id)").fetchone()[0]
print("joined cents:", wrong)
print("payment cents:", correct)
db.close()
expected output
joined cents: 40000
payment cents: 30000Engineering deduction
- scene
- Interview hypothesis: The user asks "How much did China Merchants Bank Chongqing spend today?" The database has multiple accounts and reversal flows.
- design decisions
- Analyze region, bank, time zone and expenditure definition, and generate controlled aggregation queries with server-side permissions.
- Verify target
- The amount can be independently reviewed through samples, and unauthorized accounts will not be returned or cached.
- applicable boundary
- Financial fields and calculation definitions need to be confirmed by the actual business person in charge.
Continuous questions and answers
Continue reading along with the premises and constraints of the problem. Understand the reference answers first, then try to put away the answers and explain the cause and effect and trade-offs in your own words.
Level 1Does SELECT have no side effects?
From syntax limitations down to the actual execution capabilities of the database.
Reference answer
Not necessarily. SELECT can call database functions; PostgreSQL's VOLATILE function allows the database to be modified, and some functions may also cause external effects. Also required are minimum privileges, prohibition of dangerous functions, read-only transactions, AST allowed list and resource restrictions. Keyword checking cannot be treated as complete isolation, nor can you rely solely on RLS and ignore role bypass capabilities.
Level 1How to diagnose the amount doubled after JOIN?
Use duplicated numbers to expose a query-granularity problem rather than a syntax problem.
Reference answer
Check the row count and cardinality of the business primary key before and after the JOIN to find one-to-many or many-to-many expansion. The balance first determines the unique value in the account with snapshot granularity, and then aggregates it; for transaction filtering, you can aggregate transactions first or use EXISTS to avoid copying balances. Detailed reconciliations are maintained and cannot be corrected by dividing the final total by some average multiple.
Follow this answer further
Level 2Can I use SUM(DISTINCT balance) to remove JOIN duplication?
After the parent question positioning is repeated, the common fix is to mistake the unique value for the unique entity.
Reference answer
No. It deduplicates by numerical value, not by account entity; two different accounts worth 100 yuan will be counted as one. Unique records should be restored on account and snapshot keys, or balances JOINed into multiple transactions should be avoided. It can be tested using the counterexample of two accounts with the same amount and multiple transactions each.
Follow this answer further
Level 3The balance table itself also has multiple snapshots. Is it enough to remove duplicates by account_id?
After entity deduplication, the new time dimension changes the definition of uniqueness again.
Reference answer
It is still not enough. You must first clarify the time point required by the user and the snapshot selection rules, and then use account_id plus snapshot_time or a valid version to determine one. Picking the last line based on the arrival time may select supplementary historical data; if there is no credible time point, you cannot randomly pick the maximum balance or any line. Granular repair must match both entity and business time.
Level 1Are the accounts of CMB Chongqing Branch and all CMB accounts in Chongqing the same?
When the entity names are similar, the business definition should still be retained.
Reference answer
Not the same. A branch is an organization or account-opening institution, and a region is a geographical scope. The two may cover different account sets. Check the entity dictionary and relationship to confirm whether the user refers to the branch where the account is opened, the account belongs to, or the city where the account is located; clarify if you are not sure. Write down the actual screening criteria in the answer, and limit the scope by the intersection of trusted permissions.
Draw inferences from one example: If the conditions change, how to deduce it?
First find out the conditions for change, and then determine which premises in the original plan still hold true. The following cases are teaching deductions to facilitate the transfer of principles to new problems.
Book balance changed to available balance
Changing conditions:Indicator name changes, entity collection remains the same
Extended question:Can I just change the column name and reuse all the logic?
Derivation and reference solutions
First check whether the available balance has been deducted and frozen, whether credit is included, and whether the update time is consistent with the book balance. If the table is from another granularity or snapshot, also review the JOIN and point in time. Caliber changes may affect calculation rules rather than just column mapping; the output makes clear balance types and data aging.
The principles that remain unchanged:Indicator semantics and granularity must match. The existence of a field does not mean that the calculation rules is equivalent.
One answer includes total amount and details
Changing conditions:From single query to multiple queries
Extended question:Both SELECTs are correct, so why are the total and details still inconsistent?
Derivation and reference solutions
The data may have changed between the two readings, or the definition may be different. Fixed account, time, currency and authorization scope, use the same snapshot or single query output as appropriate; mark data as of time. If the snapshot cannot be consistent across data sources, indicate the consistency window and check the version instead of having the model adjust the numbers to be consistent.
The principles that remain unchanged:Interpretable answers come from the same computational contract, and multiple correct queries still need to be entered consistently.
Easy to make mistakes
- The prompt requires reading only to be considered safe.
- The model directly specifies tenant conditions
- If SQL can run, it means the business answer is correct.
References
It is designed based on public technical information; the reference materials support the technical mechanism, and the scenarios and scoring standards are designed by this website and do not represent the original interview questions of a certain company. New Q&A and migration cases are added for principle explanation, and source verification and case operation verification are recorded separately.
Check how far you understand
After reading, you can explain the principles, boundaries, and trade-offs against these standards. It is up to you to evaluate your mastery; if further verification is needed, complete the small tasks below.
- Basic standards met
- Can do parameter binding, read-only account and range restrictions.
- Intermediate and advanced signals
- Can interpret metric semantics, time zones, row-level isolation and query resource control.
- Senior Signal
- Counterexamples can be used to check JOIN amplification, correction, caching and high-privilege bypass.
View verification records for independent examples
Hands-on verificationComplete on demand · Suggestions15 minutes
Design the "Today's Bank Expenditure" query contract and give a counterexample where JOIN leads to repeated accumulation.
Expand acceptance requirements and checkpoints
- Explicit business definition
- Permissions are determined by trusted identities
- Amount certainty review
Key inspections
- Mapping natural language to indicator definition
- Understand the limitations of read-only and AST validation
- Can find repeated accumulation of permissions and JOIN