Comps check about model-res-avm HOT 1 CLOSED

Thanks @jeancochrane for investigating. Pasting our discussion here for @julia-klauss and @dfsnow and closing for now.

Query to check dupe comps:

WITH unpivoted_comps AS (
    SELECT pin, comp_pin
    FROM (
        SELECT
            pin,
            ARRAY[
                comp_pin_1, comp_pin_2, comp_pin_3, comp_pin_4, comp_pin_5,
                comp_pin_6, comp_pin_7, comp_pin_8, comp_pin_9, comp_pin_10,
                comp_pin_11, comp_pin_12, comp_pin_13, comp_pin_14, comp_pin_15,
                comp_pin_16, comp_pin_17, comp_pin_18, comp_pin_19, comp_pin_20
            ] AS comp_array
        FROM model.comp
        WHERE run_id = '2024-03-17-stupefied-maya'
    ) AS comp_data
    CROSS JOIN UNNEST(comp_array) AS comp_pin(comp_pin)
)
SELECT pin, COUNT(comp_pin) AS num_comps, COUNT(DISTINCT comp_pin) as num_distinct_comps 
FROM unpivoted_comps
GROUP BY pin
HAVING COUNT(DISTINCT comp_pin) < COUNT(comp_pin);

What we learned:

• Results suggest that 137,094 / 1,098,988 or about 13% of PINs have a PIN show up more than once in their set of 20 comps
• But if we restrict the query to just look for dupes in the top two comps (comp_pin_1 and comp_pin_2) we only get 6,204 results, or 0.6% of PINs

Therefore, when analyzing comps, we should take care to not assume distinct rows for PINs in the training data and in the comps. Such that:

• if a PIN only appears once in a comp set, it's reasonably a safe assumption to select its most recent sale.

• if a PIN appears more than once in a comp set, assign the most recent sale to the comp with the higher comp_score, then the next lowest comp_score to the next most recent sale

from model-res-avm.