house_ad_queries.py

import re
from functools import wraps


def _quote_identifier(identifier: str) -> str:
    """Quote SQL identifiers that contain special characters."""

    def quote_part(part: str) -> str:
        if re.match(r"^[A-Za-z_][A-Za-z0-9_]*$", part):
            return part
        return f'"{part}"'

    return ".".join(quote_part(p) for p in identifier.split("."))


def _sanitize_table(func):
    @wraps(func)
    def wrapper(table, *args, **kwargs):
        table = _quote_identifier(table)
        return func(table, *args, **kwargs)

    return wrapper


@_sanitize_table
def get_main_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


@_sanitize_table
def get_flex_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        bucket,
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


@_sanitize_table
def get_bidder_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        body[0]:slotTargeting:hb_bidder[0]::varchar as HB_BIDDER, 
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


@_sanitize_table
def get_deal_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        body[0]:slotTargeting:hb_deal[0]::varchar as HB_DEAL, 
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


# New function for Ad Unit Data
@_sanitize_table
def get_ad_unit_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        split(body[0]['adUnitPath'],'/')[2]::varchar as Ad_Unit,
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


# New function for Browser Data
@_sanitize_table
def get_browser_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
         case 
                when lower(useragent) like '%edg%' then 'Edge' 
                when (lower(useragent) like '%cros%' or lower(useragent) like '%chrome%' or lower(useragent) like '%crios%') then 'Chrome' 
                when lower(useragent) like '%firefox%' then 'Firefox' 
                when lower(useragent) like '%applewebkit%' then 'Safari' 
                else 'other' 
                end as browser, 
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


# New function for Device Data
@_sanitize_table
def get_device_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
         case 
                when (useragent like '%Windows%' or useragent like '%Macintosh%') THEN 'desktop' 
                when (useragent like '%Android%' or useragent like '%iPhone%' or useragent like '%Mobi%') THEN 'phone' 
                when (useragent like '%iPad%' or useragent like '%Tablet%') THEN 'tablet' 
                else 'other' 
                end as device, 
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


# New function for Random Integer Data
@_sanitize_table
def get_random_integer_query(
    table, start_datetime, end_datetime, message_filter, campaign_id
):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        body[0]:siteTargeting:ri[0]::varchar as Random_Integer,
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


# New function for hb_pb Data
@_sanitize_table
def get_hb_pb_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        body[0]:slotTargeting:hb_pb[0]::varchar as hb_pb,
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """


# New function for hb_size Data
@_sanitize_table
def get_hb_size_query(table, start_datetime, end_datetime, message_filter, campaign_id):
    return f"""
    SELECT 
        to_date(convert_timezone('UTC', 'America/New_York', datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC', 'America/New_York', datetime)) AS EST_MINUTE,
        body[0]:slotTargeting:hb_size[0]::varchar as hb_size,
        count(*) as CNT
    FROM {table}
    WHERE convert_timezone('UTC', 'America/New_York', datetime) BETWEEN '{start_datetime}' AND '{end_datetime}'
      and message in ('{message_filter}')
      and body[0]:campaignId::varchar in ('{campaign_id}')
      and body[0]:slotTargeting:hb_pb[0]::DOUBLE >= 0.15
    GROUP BY ALL
    """