smpetersgithub/advancedsqlpuzzles

Welcome to my GitHub repository. I hope you enjoy solving these puzzles as much as I have enjoyed creating them.

advancedsqlpuzzles's Issues

Convert DDL to Snowflake - Not an issue any more, just some code others might benefit from.

Scott:

Love your product! But forget this request. Short version, I use Snowflake, and I need to make some tweaks to get it there, but I'm good.

For reference, here is what I run to get on Snowflake:

        #
        # Convert SQL Server to Snowflake.
        #
        sed -i 's/SET NOCOUNT ON;//g' puzz.sql     # Directive at the top.
        sed -i 's/\[/\"/g' puzz.sql                # Replace opening bracket.
        sed -i 's/\]/\"/g' puzz.sql                # Replace closing bracket.
        sed -i 's/, Year)/, "Year")/g' puzz.sql    # One more in the PK clause.
        sed -i 's/^GO$//g' puzz.sql                # SQL Server's "Go" command.
        sed -i 's/MONEY/DECIMAL(38,4)/g' puzz.sql  # Change MONEY to DECIMAL.
        sed -i 's/USE test;//g' puzz.sql           # Stay in one database.
        sed -i 's/MAX/4000/g' puzz.sql             # Really big string.

Thanks!

James

Puzzle 21: Average Monthly Sales

It should be changed from this

Iowa would not show in the result set because Customer ID 3003 did
not have an average monthly value over $100 in May 2018.

to this in the problem statement

Iowa would not show in the result set because Customer ID 4004 did
not have an average monthly value over $100 in May 2018.

Simply change the CustomerID from 3003 to 4004. In May, 4004 has an average sales of 75 and 3003 doesn't even exist.

Puzzle 35

Hey,
I really appreciated you work, but there is a missing solution to puzzle 35 in file named 'Advanced SQL Puzzles Solutions'.
Instead of solution there is alternative answer to to puzzle 34.

Puzzle 1 - Dance Partners

The text for puzzle 1 states:

“Note there is a mismatch in the number of students, as one female student will be left without a dance partner. Please include this individual in your list as well.”

Shouldn’t this state “… as one male student will be left without …”?

Two minor errors in Advanced SQL Puzzles DDL.sql

Love this project!!!

In the file Advanced SQL Puzzles DDL.sql, there are two minor errors:

• Line 335 should be /* to start the comment rather than just *.
• Line 646 has an extra comma at the end.

Not sure if these cause issues in SQL Server, but when I run them on Snowflake, they do, and they're tricky to fix with sed, so hoping you can fix them in the source.

I have to make a bunch of other changes to work in Snowflake, but the syntax is distinct enough that it's not an issue.

Thanks!

Puzzle 17 De-Grouping

AdvancedSQLPuzzles/Advanced SQL Puzzles/Advanced SQL Puzzles DDL.sql

Line 401 in e081714

('Eraser',3),('Pencil',4),('Sharpener',2);

Puzzle 17 De-Grouping Duplicate

AdvancedSQLPuzzles/Advanced SQL Puzzles/Advanced SQL Puzzles Solutions.sql

Line 770 in e081714

('Eraser',3),('Pencil',4),('Sharpener',2);

Puzzle 20 Price Points

AdvancedSQLPuzzles/Advanced SQL Puzzles/Advanced SQL Puzzles Solutions.sql

Line 928 in f7ade1b

Price Points

Puzzle 18 Seating Chart

AdvancedSQLPuzzles/Advanced SQL Puzzles/Advanced SQL Puzzles Solutions.sql

Line 815 in 66533d0

WITH cte_Gaps AS

Puzzle 16 Reciprocals

AdvancedSQLPuzzles/Advanced SQL Puzzles/Advanced SQL Puzzles Solutions.sql

Line 738 in e081714

(4004,3003,125),(4004,1001,125);

Advanced SQL Puzzles DDL.sql

Hi!

Found a few small issues in "In Advanced SQL Puzzles DDL.sql" file. Mostly re. uppercase/lowercase which my server is sensitive to.

• Row 284 - The query referes to "Workflow" instead of "WorkFlow".
• Row 334 - A CRLF between / and * is breaking the comment
• Row 1075 - "StartDAte" instead of "StartDate"
• Row 1126 - "ScheduleID" instead of "ScheduleId"
• Row 1134 - "ScheduleID" instead of "ScheduleId" and "StartTIme" instead of "StartTime"

/Mike