6 kyu

Customers with Urgently Expiring Certificates

139bornForThis
Description
Loading description...
SQL
Databases
View
AllIssues1Questions1SuggestionsShow Resolved
  • Please sign in or sign up to leave a comment.
  • hobovsky Avatar

    Nitpick: using a real-life value which is ssn as a technical identifier, and, even worse, as a key, is (in general) not a good thing. Customers already have a synthetic ID, and it's the best thing to use as a key.

    • dfhwze Avatar

      Also, many countries have regulations how to store ssn, if even allowed. This is personal data, even if it's somewhat publicly available.

  • dfhwze Avatar

    I don't understand your kata. What is 'customer_id' doing in table 'certs'?

    select * 
from certs ce

    id ssn cert_num cert_start cert_finish customer_id

    I thought 'ssn' was used as foreign key between these tables.

    • bornForThis Avatar

      oops, again leftover from the initial phase of kata creation :) Made foreign key correct

    • dfhwze Avatar
       
        { ssn: '777-77-7777', name: 'Grace Hall' }, # One cert expiring in less than 14 days, others expired
  { ssn: '777-77-7776', name: 'Grace Hall' }, # Same name - check ordering

      then

      customers_data.each { |c| customers.insert(c) unless customers.where(ssn: c[:ssn]).count > 0 }

      and then in expected:

      Grace Hall    777-77-7776    C13    2024-01-15    2024-02-19

      How is it even possible to get '777-77-7776' in the expected results, if it's not even being added to the input to begin with ..

    • Voile Avatar

      That's the issue I raised below, maybe you need to reload the test cases

    • bornForThis Avatar

      That is wrong from my side. cert_num should be unique, while I somehow made duplicate code names which resulted in a wrong expected output. Sorry, not my day :/ I fixed tests and made cert_num unique in DB schema

    • hobovsky Avatar

      Potentially interesting fact: IIRC, for X.509 certificates, serial numbers have to be unique per issuer, and it is possible to have two certificates, issued by different CAs, with the same serial nuber. This could potentially add an interesting twist to your kata :)

  • Voile Avatar

    Order the results by the customer's name then by certificate expiration date, and finally by certificate number - all in ascending order.

    Random test is using the wrong order:

    The fixed test in text fixture is wrong too (it contains 1 more row). The sample test is correct, however.

    • bornForThis Avatar

      I updated the description - made initially as by date but then understood that better to have SNN as a second criterion, forgot to update. Also made fixed tests consistent. Thanks for the feedback!

      Issue marked resolved by bornForThis 13 months ago
  • dfhwze Avatar

    Spec not clear:

    Include all such certificates for each customer, if they have more than one.

    Does this mean "only if" or do you just want to say there could be one or more of such certificates per customer?