Account codes in ledger_accounts are text identifiers: 007 and 7 are different accounts. Write a query to return every row in postings with:
02account_code: account_ref with ordinary spaces trimmed from both ends; preserve NULL.
03account_name: The name whose ledger code exactly equals that trimmed text, or NULL if no account matches.
04collides_with: All other ledger account codes that would equal the posting’s code if both were read numerically. Sort those codes as text and join them with commas, or return NULL if there are none.
Calculate collisions even when the posting has no exact ledger match. Exclude the posting’s own exact text code from collides_with.
Ordering: Sort by posting_id in ascending order.
Returnposting_idaccount_codeaccount_namecollides_with
About this problem
You practise telling the difference between a number and an identifier that happens to be written with digits. A ledger stores 007, 7, 0070 and 70 as four separate accounts, the posting feed pads some references with spaces, and you match each posting to its account by text while also reporting which accounts a numeric conversion would have merged. The skill is applying two different comparison rules in one query and knowing why each belongs where it is.
This problem is the mirror image of problem 09 in this set, and the contrast is the lesson. There, the key was a number written inconsistently as text: 02 and 2 really are the same customer. Here the leading zeros carry meaning, so the same conversion destroys the data – join on TRY_CAST(account_code AS BIGINT) and the query returns fourteen rows instead of eight, with posting 3 booked to Petty cash and posting 8 to the wrong account entirely. Nothing errors, and the row count going up rather than down is what makes it hard to spot in review. Padding the codes with lpad to a common width is the same mistake in nicer clothes. The only question that settles it is whether two values that differ by a leading zero are the same thing in the business, and the answer lives in the ledger, not in SQL.
Read the lesson: Data Types & CAST (P13) →