Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
896 B
2
Indexable
Never
SELECT_DATA_FOR_COMMON_POOL_STATS = """
    WITH counted_pools AS (
        SELECT DISTINCT pool_id
        FROM markup_meta.common_pool_stats
    ),
    markup_data AS (
        SELECT author_id, doc_hash_id, query_type, pool_id
        FROM %(schema_name)s.markup
        WHERE 1=1
            AND (pool_id = '%(pool_id)s' OR pool_name = '%(pool_name)s')
    ),
    not_counted_pools AS (
        SELECT
            m.author_id,
            m.doc_hash_id,
            m.query_type AS author_result
        FROM markup_data m
        LEFT OUTER JOIN counted_pools cp
            ON m.pool_id = cp.pool_id
        WHERE 1=1
            AND cp.pool_id is NULL
    )
    SELECT
        ncp.author_id,
        ncp.author_result AS prediction,
        a.query_type AS label
    FROM not_counted_pools ncp
    INNER JOIN %(schema_name)s.%(schema_name)s a
        ON ncp.doc_hash_id = a.doc_hash_id
"""