Similar to the Advent of Code, the Hanukkah of Data (archive) was released in December 2022.

I don’t have time to do that during the holidays, so January 2023 it is for me.

1. Plan of attack

We’re looking for cleaners in front of Noah’s shop, so they probably bought the cleaner here.

So I’m looking for:

  1. anybody who bought some rug cleaner

  2. in 2017

  3. with only 2 names

  4. whose initials are "J D"

2. Implementation

Most of it can be done directly in SQL, but people with more than 2 names will also be selected, and so they need to be filtered out later on.

I’m going to read the db from Java (17), and handle the rest in code:

package org.example;

import org.sqlite.SQLiteDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;
import java.time.Instant;
import java.util.Arrays;

public class Puzzle2 {
  public static void main(String[] args) {
    final var startTime = Instant.now();
    Puzzle2.run();
    final var endTime = Instant.now();
    System.out.println("Duration: " + Duration.between(startTime, endTime).toString());
  }

  private static void run() {
    final var dbUrl = "jdbc:sqlite:/path/to/noahs.sqlite";

    final var dataSource = new SQLiteDataSource();
    dataSource.setUrl(dbUrl);

    final String query = """
        select
          distinct name,
          phone
        from
          customers c
        join products p on
          desc = 'Rug Cleaner'
        join orders_items oi on
          oi.sku = p.sku
        join orders o on
          o.customerid = c.customerid
          and o.orderid = oi.orderid
        where
          strftime('%Y', o.ordered)= '2017'
          and upper(c.name) like 'J% D%'
        order by
          name;
        """;
    try (final Connection connection = dataSource.getConnection();
         final Statement statement = connection.createStatement();
         final ResultSet results = statement.executeQuery(query)) {

      while (results.next()) {
        final var name = results.getString("name");
        final var phone = results.getString("phone");
        final var names = Arrays.asList(name.split(" "));
        if (names.size() == 2) {
          var initials = names.get(0).charAt(0) + "" + names.get(1).charAt(0);
          initials = initials.toUpperCase();
          if (initials.equals("JD")) {
            System.out.println("Match found: " + name + "/" + phone);
          }
        }
      }
    } catch (final SQLException e) {
      e.printStackTrace(System.err);
    }
  }
}

3. Result

It turned out that the SQL filter only returned 2 results in 1.4s, and the post filtering didn’t change that.

Trying both shows that one of them is correct.

Time to solve: 27 minutes.