Six queries, which index serves which

import java.util.*;
import java.util.stream.*;

/**
 * Challenge: six queries, three indexes. Which index serves which?
 *
 * Two of the six cannot use any index for a reason that is visible in the SQL
 * text. One can use an index and should not. One needs an index that does not
 * exist yet. Decide all six before running anything — the checker at the
 * bottom only tells you whether an index CAN be used, which is not the same
 * question as whether it SHOULD be.
 */
public class Starter {

    record Index(String name, List<String> columns) {
        static Index of(String name, String... cols) { return new Index(name, List.of(cols)); }
    }

    static final List<Index> INDEXES = List.of(
        Index.of("idx_cust_status", "customer_id", "status"),
        Index.of("idx_email", "email"),
        Index.of("idx_created", "created_at"));

    static final String[] QUERIES = {
        "1  WHERE customer_id = 42 AND status = 'OPEN'",
        "2  WHERE status = 'OPEN'",
        "3  WHERE LOWER(email) = 'ana@example.com'",
        "4  WHERE company_name LIKE '%ltd'",
        "5  WHERE created_at >= '2026-01-01' ORDER BY created_at",
        "6  WHERE customer_id = 42 ORDER BY total DESC",
    };

    public static void main(String[] args) {
        System.out.println("indexes:");
        INDEXES.forEach(i -> System.out.println("  " + i.name() + " " + i.columns()));
        System.out.println();
        System.out.println("queries:");
        for (String q : QUERIES) System.out.println("  " + q);

        // TODO 1: verdict for each, BEFORE any code. For every query write
        // either the index that serves it, or the reason none can.
        //
        //   1 : ____
        //   2 : ____
        //   3 : ____
        //   4 : ____
        //   5 : ____
        //   6 : ____

        // TODO 2: two of them cannot use an index for a reason you can see in
        // the SQL. Name both reasons, then rewrite each query — or say what
        // would have to change in the schema — so an index becomes usable.

        // TODO 3: query 2 filters on `status`, which IS in idx_cust_status.
        // Say why that does not help, using the words "leftmost prefix". Then
        // say what would make it work, and what that new index would cost.

        // TODO 4: suppose status has three values and OPEN is 40% of the
        // table. Even with a perfect index on status, the planner will scan.
        // Explain why that is the correct decision, in terms of random reads
        // versus sequential ones.

        // TODO 5: query 6 filters on customer_id and sorts by total. Say
        // exactly which index would let the database skip the sort, and write
        // the CREATE INDEX statement.

        // TODO 6: write `boolean serves(Index, equalityColumns, orderBy)`
        // implementing the leftmost prefix rule, and check your six verdicts
        // against it. Then say which two of your verdicts the checker CANNOT
        // confirm, and why that is a limitation of the rule rather than of
        // your code.
    }
}