Week 34 – Intermediate

Time to help out our SuperAccountant

DataSuperHero Inc. is trying to connect to one of its data stores but sadly, the data that has been output is structured differently than SuperAccountant (“Who do you call when Tax Season comes?”) would like it to be.

The data that we start with, is structured with a parent-child system but there are some caveats :

• The depth of the child/parent varies in the structure but is given for every ‘chain’
• If the code has no child, the flag ‘lowest_level’ is set to 1
• The highest depth in the entire system is 7
• There is a valid_from and valid_until value. You have to compare this to the current date

What SuperAccountant would like to see is the following :

• using columns called “level_1”, “level_2” etc. construct the data in such a way that a single chain gets displayed on a single line
• If a code doesn’t have 7 levels, fill out the missing levels with the last known value
• The end table should only have rows of data using chains that are ‘finished’. You don’t need to display a row for every step of the chain

Visual aid for the end result :

The startup code :

create or replace table start_data(
    code VARCHAR
  , code_parent VARCHAR
  , valid_until DATE
  , valid_from DATE
  , is_lowest_level BOOLEAN
  , max_level INTEGER
)
as
select * from values
    ('CC0193','EBGABA','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',1,7)
  , ('CC0194','EBGABA','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',1,7)
  , ('EBGABA','EBGAB','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EBGAB','EBGA','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EBGA','EBG','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EBG','EB','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('EB','ZZ','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('ZZ',NULL,'9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,7)
  , ('7050307','CC','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',1,3)
  , ('CC','A1','9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,3)
  , ('A1',NULL,'9999-01-01 00:00:00.000','1950-01-01 00:00:00.000',0,3)
;

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge