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
"""