Using a window aggregate would presumably be substantially faster than the self-join that you currently have. This is available from version 3.25

Unfortunately, in SQLite, DISTINCT cannot be used in an OVER aggregate, but we can simulate it with MAX of DENSE_RANK (if file_id is nullable then it can be a little more complex):

WITH file_id_ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY hashband ORDER BY file_id) rnk
    FROM hashbands
),
file_id_counts AS (
    SELECT *,
        MAX(rnk) OVER (PARTITION BY hashband) AS count
    FROM file_id_ranked
)
SELECT hashband, file_id, window_id
    FROM file_id_counts
    WHERE count > 1
    ORDER BY hashband;

I strongly recommend an index on hashband, file_id, window_id in that order.

Using a window aggregate would presumably be substantially faster than the self-join that you currently have. This is available from version 3.25

Unfortunately, in SQLite, DISTINCT cannot be used in an OVER aggregate, but we can simulate it with MAX of DENSE_RANK (if file_id is nullable then it can be a little more complex)

The calculation is quite simple: DENSE_RANK will give us a ranking, but with tied results, e.g. 1,1,2,3,3,3,4. Then we use MAX to get the highest of that. PARTITION BY works like GROUP BY but for window aggregates, in other words the function is calculated separately over each partition.

WITH file_id_ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY hashband ORDER BY file_id) rnk
    FROM hashbands
),
file_id_counts AS (
    SELECT *,
        MAX(rnk) OVER (PARTITION BY hashband) AS count
    FROM file_id_ranked
)
SELECT hashband, file_id, window_id
    FROM file_id_counts
    WHERE count > 1
    ORDER BY hashband;

I strongly recommend an index on hashband, file_id, window_id in that order.

Using a window aggregate would presumably be substantially faster than the self-join that you currently have. This is available from version 3.25

Unfortunately, in SQLite, DISTINCT cannot be used in an OVER aggregate, but we can simulate it with MAX of DENSE_RANK (if file_id is nullable then it can be a little more complex):

WITH file_id_ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY hashband ORDER BY file_id) rnk
    FROM hashbands
),
file_id_counts AS (
    SELECT *,
        MAX(rnk) OVER (PARTITION BY hashband) AS count
    FROM file_id_ranked
)
SELECT hashband, file_id, window_id
    FROM hashbands
    WHERE count > 1
    ORDER BY hashband;

I strongly recommend an index on hashband, file_id, window_id in that order.

Using a window aggregate would presumably be substantially faster than the self-join that you currently have. This is available from version 3.25

Unfortunately, in SQLite, DISTINCT cannot be used in an OVER aggregate, but we can simulate it with MAX of DENSE_RANK (if file_id is nullable then it can be a little more complex):

WITH file_id_ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY hashband ORDER BY file_id) rnk
    FROM hashbands
),
file_id_counts AS (
    SELECT *,
        MAX(rnk) OVER (PARTITION BY hashband) AS count
    FROM file_id_ranked
)
SELECT hashband, file_id, window_id
    FROM file_id_counts
    WHERE count > 1
    ORDER BY hashband;

I strongly recommend an index on hashband, file_id, window_id in that order.