[Community Puzzle] Parse SQL Queries


Send your feedback or ask for help here!

Created by @AntiSquid,validated by @Crypticsy,@LaurentValade and @DaNinja.
If you have any issues, feel free to ping them.

Why does the last table begin with spaces?


Without a schema how can we know a heading of “Weight” and data of 15 means it is numeric 15 or a string of “15”?

Better write it down if there are any untold assumptions about data types.


Nice puzzle but yes it must be stated somewhere that you sort by numeric value if the type looks numeric, else by lexicographic order.

1 Like

Not sure, thought i trimmed that text already. Trimmed now and resubmitted.

Added a note regarding numerical values.

sort numerical columns by their numerical value

It works if there is a definition of “numerical columns”.

Normally a numeric value matches the regex \d*\.?\d+ but in this puzzle it’s even simpler, basically if the first char of an element is a digit you can assume that the whole column has a numeric type.
And if you need to chose between int and float, just check if there is a “.”
But I agree that an additionnal input line with types would help.

1 Like

An extra dataType line in input could be too late to add now.

I’m fine with any kinds of definition about “numeric”. If the author intends to define a numeric column as “the relevant field in the first row of data starts with a digit then the whole column is numeric”, that’s great, and should have been documented in the statement. Allowing every coders to make their own assumptions by so-called common sense is an excuse of imprecise statement.

Besides string vs numbers, the statement is also missing a description of all possible data types allowed in the puzzle. We do not know is boolean or any other types of columns possible. We do not know will there be null or empty fields in the table.

This is not CoC reverse mode that coders should guess the rules by reading test cases. All rules, constraints and assumptions that could affect design decisions should be written in statement.

1 Like

Sounds like you want an overhaul of inputs and it would definitely complicate things. There’s a certain amount of background knowledge expected with any given puzzle. In this case it’s minimal really.

If it’s numeric value it’s numeric data. There are functions such as isnumber to ease up things.

Also there are no missing values or other issues which might actually occur in a real SQL table.

No, do not crash existing solutions.
I just recommend providing better definition in the statement to clarify the problem.

What is “numeric” is an assumption, an opinion different people can have different conclusion in different situations. If you have an assumptions in mind of how to test numerical, write it down to make it a specification.

Examples: "When you find the full column of data in a table is
[composed of digits] or [conform to a regex " \d*\.?\d+“], this column is numeric.” - See, specifying (1) how to test, (2) the extend of testing.

Or, provide an exhaustive list… “Whenever you find the header is [Price, Age, Year, Count, ID, …] these columns are numeric.”

My PHP code fails validation 2 “Drop Tables” but the Goal states “For simplicity there are no commands to manipulate tables and only one table to select from. Nothing more advanced than basic SELECT.” and there is nothing in the Goal or Input sections to suggest that tables will need to be dropped or what output is expected.

My code also fails validation 4 “Ternary Operator” but the Goal states “Since this is an easy puzzle the only condition you need to check for is if the values are equal.” and no examples of ternary operators appear in the Goal or Input sections to suggest that they are expected or how to deal with them.

I’m stumped as to what to do?

Test case and validator names are by themselves misleading :unamused:. You can assume that validators look alike the test case sturctures.

A trap is about identifying numerical columns and numerical values.
Remember “numbers” include both integers and float-point numbers.


Ahhh. My [double] bad for not reading the goal as closely as I thought I had then taking the validator titles at face value and looking in the wrong places for the problem.


Good puzzle, thanks!

1 Like

I just started working on this, and it looks like a good (very educational) puzzle.

Question: Are there Multiple conditions in some of these (in other words, things after “WHERE” that are “OR” or “AND”)?

If so, why is there not an example of that in a test case?


1 Like

No, they are not tested.

There are no multiple conditions.