Code Kata for SQL – Toy Story

by Greg M Lucas on 27 August 2012

Practicing code kata is an established practice in agile shops but many kata are designed with object-oriented languages in mind and do not not always lend themselves to being reproduced in a declarative, set-based language like T-SQL.  So I have created this new kata specifically for SQL. Enjoy…

User Story

My name is Milt Pixney and I think I know a bit about SQL although the DBA won’t let me read to or write from any tables or even execute any stored procedures. I want to be able to write simple queries against a summary of recent toy sales aggregated by year, quarter and category so that I can provide a range of MI reports to the business.

Suggested solution: Create a view or user-defined function to summarise the value of toy sales by year/quarter/category against which Milt can run his queries. The code for the tables is at the bottom of this post but you should create your own test data according to the tests you write.

Business Rules

  1. Recent is defined as the last two complete years – 2010 and 2011 if you’re reading this in 2012
  2. Show data for all Year, Quarter and Toy Category combinations – even if there were no applicable sales
  3. Any sales of toys with no category should rollup to an “unknown” category for each year and quarter

Possible Tests

  • Only data for 2010 and 2011 is returned (consider what happens if there is no data for either year?)
  • There is a value for every quarter in 2010 and 2011 regardless of whether there is any sales data
  • Each quarter is listed for each year regardless of whether there are any applicable sales in that quarter
  • Each category is listed for each quarter regardless of whether there are any applicable sales in that category
  • Months are correctly translated or transposed to quarters
  • Totals are aggregated correctly, consider how NULL SalesValue and NULL ToyCategoryId are handled
  • Sales of toys with no sales category should appear as category = “Unknown”

Possible re-factoring or future enhancements

For any of the following changes, it is important that all the old functionality is still supported.  You should already have sufficient unit tests to guarantee this.

  • Where a total is for a partial quarter this should be indicated somehow (1-3, 4-6, 7-9 and 10-12 are the standard quarters)
  • Make the query dynamic so that it shows data from the last two years on a rolling basis (i.e. the last 24 months including partial years)
  • Change the query so that only categories with at least one toy active in the quarter are visible (based on DateLaunched and DateDiscontinued)
  • Roll up the aggregate values to show annual totals for each category
  • Milt’s SQL skills aren’t as good as he thinks so you might want to include some indicator of how the totals are grouped.

The Code

IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[MonthlySalesSummary]'), N'IsUserTable') = 1 DROP TABLE [dbo].[MonthlySalesSummary]
IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[Toy]'), N'IsUserTable') = 1 DROP TABLE [dbo].[Toy]
IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[ToyCategory]'), N'IsUserTable') = 1 DROP TABLE [dbo].[ToyCategory]
GO

CREATE TABLE ToyCategory
(
  Id int NOT NULL
, Name varchar(32) NOT NULL UNIQUE
, CONSTRAINT PK_ToyCategory PRIMARY KEY (Id)
, CONSTRAINT AK_ToyCategory_Name UNIQUE (Name)
) ;

--! Add a default entry
INSERT ToyCategory VALUES (0, 'Unknown');

CREATE TABLE Toy
(
  Id int NOT NULL IDENTITY(1,1)
, Name varchar(16) NOT NULL
, ToyCategoryId int NULL
, DateLaunched datetime NOT NULL CONSTRAINT DF_Toy_DateLaunched DEFAULT GETDATE()
, DateDiscontinued datetime NULL
, CONSTRAINT PK_Toy PRIMARY KEY (Id)
, CONSTRAINT AK_To_Name UNIQUE (Name)
, CONSTRAINT FK_Toy_ToyCategory FOREIGN KEY (ToyCategoryId) REFERENCES ToyCategory (Id)
) ;

CREATE TABLE MonthlySalesSummary
(
  SalesYear smallint NOT NULL -- e.g. 2010, 2011
, SalesMonth tinyint NOT NULL
, ToyId int NOT NULL
, SalesValue decimal(18,2) NULL
, CONSTRAINT PK_MonthlySalesSummary PRIMARY KEY (SalesYear, SalesMonth, ToyId)
, CONSTRAINT FK_MonthlySalesSummary_Toy FOREIGN KEY (ToyId) REFERENCES Toy (Id)
, CONSTRAINT CK_MonthlySalesSummary_SalesMonth CHECK (SalesMonth IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
) ;

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: