Code Best Practices Checker

SQL Code Refactoring and Best Practices

This document outlines a refactoring process for an SQL query, focusing on improving readability, performance, and adherence to best practices by addressing keyword consistency, redundant casting, and logical grouping.


Empty image or helper icon

Prompt

SELECT Member_Club, memberproduct, count(BKJobNo) as eventcount, SUBSTRING(CAST(bkdatekey AS VARCHAR), 5, 2) AS extracted_month, SUBSTRING(CAST(bkdatekey AS VARCHAR), 1, 4) AS extracted_year
  FROM [EDW].[RS].[FactRoadServiceJobs] where BKDateKey BETWEEN '20191101' AND '20241231' and (PatrolUnit like '5MRR%' OR PatrolUnit like '5MR1%' OR PatrolUnit like '5MR2%' OR PatrolUnit like '5MR4%' OR PatrolUnit like '5MR6%' OR PatrolUnit LIKE '5MREV%' OR PatrolUnit LIKE '5MRSTH%') AND JobServiceCategory = 'Road Service' and IsCompletedJob = 1 and region  = 'm'
 group by SUBSTRING(CAST(bkdatekey AS VARCHAR), 5, 2), SUBSTRING(CAST(bkdatekey AS VARCHAR), 1, 4), MemberProduct, Member_Club
 order by extracted_month, extracted_year

Answer

Language/Technology Identification

The provided code snippet is written in SQL, specifically designed for querying data from a relational database.

Code Refactoring

The primary aim is to improve readability, performance, and adherence to best practices.

Issues and Refactoring Steps:

  1. Keyword Consistency: SQL keywords should be consistently capitalized to distinguish them from other identifiers easily.
  2. Column Naming: AS keywords in column aliases should be consistently used to improve readability.
  3. Redundant Casting: The data type casting from bkdatekey can be optimized.
  4. Date Functions: Use YEAR and MONTH functions directly to extract parts of the date instead of SUBSTRING for better performance and readability.
  5. Logical Grouping: Conditions within the WHERE clause can be organized for clarity.
  6. Meaningful Aliases: Use clear and consistent aliases for derived columns.

Refactored Code:

SELECT 
    Member_Club, 
    MemberProduct, 
    COUNT(BKJobNo) AS EventCount, 
    MONTH(CAST(BKDateKey AS DATE)) AS ExtractedMonth, 
    YEAR(CAST(BKDateKey AS DATE)) AS ExtractedYear
FROM 
    [EDW].[RS].[FactRoadServiceJobs]
WHERE 
    BKDateKey BETWEEN '20191101' AND '20241231' 
    AND (
        PatrolUnit LIKE '5MRR%' 
        OR PatrolUnit LIKE '5MR1%' 
        OR PatrolUnit LIKE '5MR2%' 
        OR PatrolUnit LIKE '5MR4%' 
        OR PatrolUnit LIKE '5MR6%' 
        OR PatrolUnit LIKE '5MREV%' 
        OR PatrolUnit LIKE '5MRSTH%'
    ) 
    AND JobServiceCategory = 'Road Service' 
    AND IsCompletedJob = 1 
    AND Region = 'M'
GROUP BY 
    YEAR(CAST(BKDateKey AS DATE)), 
    MONTH(CAST(BKDateKey AS DATE)), 
    MemberProduct, 
    Member_Club
ORDER BY 
    ExtractedYear, ExtractedMonth;

Documentation of Changes

1. Keyword Consistency

  • Issue: SQL keywords were inconsistently capitalized.
  • Refactoring: Ensured all SQL keywords (SELECT, FROM, WHERE, etc.) were capitalized.
  • Benefit: Improves readability and adheres to conventional SQL style guidelines.

2. Column Naming with AS

  • Issue: There was inconsistency in using AS for aliases.
  • Refactoring: Applied AS keyword for column aliases consistently (EventCount, ExtractedMonth, ExtractedYear).
  • Benefit: Enhances clarity and adherence to standard SQL practices.

3. Redundant Casting

  • Issue: bkdatekey was cast to VARCHAR, which is unnecessary for date part extraction.
  • Refactoring: Removed CAST to VARCHAR and directly cast bkdatekey to DATE.
  • Benefit: Improves performance by using direct date functions and reduces unnecessary casting operations.

4. Date Functions

  • Issue: Used SUBSTRING to extract year and month, which is less efficient.
  • Refactoring: Replaced SUBSTRING operations with YEAR and MONTH functions.
  • Benefit: Direct date functions improve readability and performance.

5. Logical Grouping in WHERE Clause

  • Issue: The WHERE clause conditions were linear and hard to read.
  • Refactoring: Organized conditions within the WHERE clause using line breaks and grouping related conditions.
  • Benefit: Enhanced readability and logical grouping.

6. Meaningful Aliases

  • Issue: Used different column names (extracted_month, extracted_year).
  • Refactoring: Changed to ExtractedMonth, ExtractedYear to keep consistent naming standards.
  • Benefit: Improves readability and maintains consistency across the query.

Conclusion

By implementing these refactoring steps, the SQL code becomes more readable, efficient, and adheres to best practices. The essential functionality is preserved while making the code more maintainable and easier to understand.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

This document outlines a refactoring process for an SQL query, focusing on improving readability, performance, and adherence to best practices by addressing keyword consistency, redundant casting, and logical grouping.

More Code Best Practices Checkers
Apache Flink Code Best Practices Checker Apache Flink Code Best Practices CheckerApache Pig Code Best Practices Checker Apache Pig Code Best Practices CheckerAzure Data Factory Code Best Practices Checker Azure Data Factory Code Best Practices CheckerC/C++ Code Best Practices Checker C/C++ Code Best Practices CheckerCouchDB Code Best Practices Checker CouchDB Code Best Practices CheckerDAX Code Best Practices Checker DAX Code Best Practices CheckerExcel Code Best Practices Checker Excel Code Best Practices CheckerFirebase Code Best Practices Checker Firebase Code Best Practices CheckerGoogle BigQuery Code Best Practices Checker Google BigQuery Code Best Practices CheckerGoogle Sheets Code Best Practices Checker Google Sheets Code Best Practices CheckerGraphQL Code Best Practices Checker GraphQL Code Best Practices CheckerHive Code Best Practices Checker Hive Code Best Practices CheckerJava Code Best Practices Checker Java Code Best Practices CheckerJavaScript Code Best Practices Checker JavaScript Code Best Practices CheckerJulia Code Best Practices Checker Julia Code Best Practices CheckerLua Code Best Practices Checker Lua Code Best Practices CheckerM (Power Query) Code Best Practices Checker M (Power Query) Code Best Practices CheckerMATLAB Code Best Practices Checker MATLAB Code Best Practices CheckerMongoDB Code Best Practices Checker MongoDB Code Best Practices CheckerOracle Code Best Practices Checker Oracle Code Best Practices CheckerPostgreSQL Code Best Practices Checker PostgreSQL Code Best Practices CheckerPower BI Code Best Practices Checker Power BI Code Best Practices CheckerPython Code Best Practices Checker Python Code Best Practices CheckerR Code Best Practices Checker R Code Best Practices CheckerRedis Code Best Practices Checker Redis Code Best Practices CheckerRegex Code Best Practices Checker Regex Code Best Practices CheckerRuby Code Best Practices Checker Ruby Code Best Practices CheckerSAS Code Best Practices Checker SAS Code Best Practices CheckerScala Code Best Practices Checker Scala Code Best Practices CheckerShell Code Best Practices Checker Shell Code Best Practices CheckerSPSS Code Best Practices Checker SPSS Code Best Practices CheckerSQL Code Best Practices Checker SQL Code Best Practices CheckerSQLite Code Best Practices Checker SQLite Code Best Practices CheckerStata Code Best Practices Checker Stata Code Best Practices CheckerTableau Code Best Practices Checker Tableau Code Best Practices CheckerVBA Code Best Practices Checker VBA Code Best Practices Checker