Order the columns for six queries

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

/**
 * Challenge: six queries on one table. Design the indexes.
 *
 * Not "which columns" — which columns, IN WHICH ORDER, and how few indexes
 * can cover the six. A naive answer is six indexes and six writes per insert.
 * A good answer is three or four.
 *
 * Work the order out from the rule before writing any code: every equality
 * column first, then the range or ORDER BY column, then anything worth
 * covering.
 */
public class Starter {

    /*
     * table orders (
     *   id, tenant_id, customer_id, status, channel,
     *   created_at, total, currency
     * )
     *
     * Q1  WHERE tenant_id = ? AND customer_id = ?
     *     SELECT id, status, total
     *
     * Q2  WHERE tenant_id = ? AND status = ? AND created_at >= ?
     *     SELECT id, customer_id
     *
     * Q3  WHERE tenant_id = ? AND customer_id = ? AND status = ?
     *     SELECT id
     *
     * Q4  WHERE tenant_id = ? ORDER BY created_at DESC LIMIT 50
     *     SELECT id, customer_id, total
     *
     * Q5  WHERE channel = ? AND created_at >= ?
     *     SELECT id
     *
     * Q6  WHERE tenant_id = ? AND total > ?
     *     SELECT id, customer_id
     */

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

    // TODO 1: for each query write its equality columns and its range or
    // ORDER BY column, before proposing anything.
    //
    //   Q1  equality: ____________________  range/order: ____
    //   Q2  equality: ____________________  range/order: ____
    //   Q3  equality: ____________________  range/order: ____
    //   Q4  equality: ____________________  range/order: ____
    //   Q5  equality: ____________________  range/order: ____
    //   Q6  equality: ____________________  range/order: ____

    // TODO 2: turn each row above into an ordered column list — that IS the
    // index for that query. Put them in CANDIDATES below.

    static final List<Index> CANDIDATES = List.of(
        // Index.of("idx_q1", "tenant_id", "customer_id"),
        // ... one per query, to start with
    );

    // TODO 3: merge them. Two rules:
    //   - drop any index that is a leading prefix of another
    //   - two queries can share an index when one equality set is a leading
    //     prefix of the other's
    // Aim for four or fewer.

    // TODO 4: exactly one of the six cannot share with the rest. Identify it
    // and say why its leading column rules it out.

    // TODO 5: pick the query most worth covering and append its selected
    // columns. Say which read that removes, and why it does not cost an extra
    // write.

    // TODO 6: write the redundancy check and run it over your final set —
    // an index is redundant when its columns are a leading prefix of another's.

    public static void main(String[] args) {
        System.out.println("candidates: " + CANDIDATES.size());
        for (Index i : CANDIDATES) System.out.println("  " + i.name() + " " + i.columns());
        System.out.println("write cost per insert: " + (1 + CANDIDATES.size()));

        // TODO 7: print the final set and its write cost. Compare with six
        // indexes, and state the read benefit you kept.
    }
}