The goal of this repository is to improve SQL programming skills with a coding challenge. The implementation of these challenges follows the methodology of Test-Driven Development, i.e.:
- Think about tests cases
- Write the tests
- Implement the code
- Test
- Refactoring
- Test again until all tests pass
The challenges are based on Codewars exercises. The SQL engine is PostgreSQL 9.6 and the tests cases are based on Behavior Driven Development of Ruby/RSpec SQL.
- Trimming the Field
- Simple Having
- Count Weekdays
- Members Approved for Voucher
- Hierarchical Structure
- Calculating Running Total
- Present XML data to SQL way
- Top 10 customer by total payments amount
- Analyzing the sales by product and date
- SQL with Harry Potter: Sorting Hat Comparators
- Top Departments
- Conditional Count
- Monsters using CASE
- Elven Wildcards
- Group by day
- Age Calculator
- Ranking Sales
- Maths with String Manipulation
- Regex Extraction
- Filtering Sales
- Using Window Functions to Get Top N per Group
- Find all Movies that Two Actors Cast in Together
- Simple table totaling
- Regex AlphaNumeric Split
- Pivoting data
- Returning Strings
- Handling with Nulls
A small set of utility methods and classes have been provided to make testing and displaying SQL queries easy with Ruby. These utility methods are available from any file in the runner.
- $sql is a global variable which contains the user's SQL solution
- run_sql is a method that will run the user's query, execute any non-SELECT statements and print and return any result sets from SQL SELECT statements.
- compare_with is a method which compares the candidate's submitted query to the reference query and auto-generates a test suite based on the expected query results.