Please ConvertToEnglish() – Obscure SQL

Sometimes you come across a simple SQL statement. Yet you’re confounded by the reasoning behind it. You think, “What is this trying to do?!”

Never had that experience before? Well, here’s your chance. Here’s a heavily anonymised version:

select max(is_active) from customers where customer_id = 'ABC'

And here’s the accompanying database table named “customers”.

customer_id customer_name is_active
ABC ABC Corporation Y
PQR PQR Company Y
XYZ XYZ Company N
XYZ XYZ MegaCorp N

Study both the SQL statement and the data in the database table. Then tell me what you think is the business logic behind the SQL statement.

What I mean is, suppose some manager needs some information. And the SQL statement is part of the solution to obtain that information. What does that manager need to know?

Need some help? Here’s an equivalent version:

select min(customer_status) from customers where customer_id = 'ABC'
customer_id customer_name customer_status
ABC ABC Corporation C
PQR PQR Company C
XYZ XYZ Company H
XYZ XYZ MegaCorp H

This entire exercise is to train you to decipher existing logic and data with very little help or documentation.

Bonus points: What do you think the values for the columns “is_active” and “customer_status” stand for?

  1. xero

    Determines if any of the customer’s aliases are active?

    No clue what the ‘H’ and ‘C’ would be.

    I’ve found it’s better to leave “comments of intention” in code than “comments of implementation”. This would be a good example of why. Though, given there are no comments around this, it may stress the more general “always comment business logic, no matter how mundane” mantra.

  2. Vincent Tan

    “Determines if any of the customer’s aliases are active?”
    Hi xero, your answer is correct. Succinct too.

    There’s supposed to be an effective date (or start date) of the alias, but I thought that would be too obvious. Given a customer ID, determine if it’s still active.

    It’s a real case I encountered while going through some old code. It relates to another concept though, not the activity of the customer. Like I said, heavily anonymised. Took me a while to figure out what my SQL statement was doing…

    As for the is_active values, they should be obvious. Y for yes, N for no.

    H and C? Historic (or history) and Current. I’ve encountered some fairly complicated single-lettered column data before, usually for status or flag type of information.

    In the problem posed, the original programmer made use of the fact that Y is greater than N, so a max would return a Y, if Y exists.

    Similarly, a min of C and H would return C, if any of the rows had C as customer_status.

    And there were no comments of intention or implementation. Actually, there were no comments at all. The program specs also don’t mention this. So it’s a specific implementation of a business logic, which may be documented, but I have no way to know which one. The only way I can know what’s going on is to decipher the code.

Comments are closed.