def user_metrics_query(table_name='%(table_name)s'):
    """
    Create aggregated features per user, day and hour. E.g. count all
    outgoing sms to create an indicator for that time period called
    og_sms. Incoming events are abbreviated with 'ic'.

    Inputs
    ------
    table_name : Name of the table the query is supposed to run on,
                 defaulting to '%(table_name)s', i.e. no substitution.

    Output
    ------
    Aggregated event counts per user, day and hour.
    """
    query = """
        SELECT
          user_id,
          day,
          hour,
          SUM(IF(direction = 'out' AND interaction = 'text', value, NULL))
           AS og_sms,
          SUM(IF(direction = 'in' AND interaction = 'text', value, NULL))
           AS ic_sms,
          SUM(IF(direction = 'out' AND interaction = 'call', value, NULL))
           AS og_calls,
          SUM(IF(direction = 'in' AND interaction = 'call', value, NULL))
           AS ic_calls,
          SUM(IF(direction = 'out' AND interaction = 'call', vol, NULL))
           AS og_vol,
          SUM(IF(direction = 'in' AND interaction = 'call', vol, NULL))
           AS ic_vol
        FROM
        (
          SELECT
            caller_id as user_id,
            DATE(datetime) as day,
            HOUR(datetime) as hour,
            interaction,
            direction,
            COUNT(*) as value,
            SUM(call_duration) as vol
          FROM %(table_name)s
          GROUP BY caller_id, day, hour, interaction, direction
        )
        GROUP BY user_id, day, hour
    """
    return query % {'table_name': table_name}
