RedaAffane

def subsets(s):
    '''
    This function returns all the possible subsets of a set of channels.
    input :
            - s: a set of channels.
    '''
    if len(s)==1:
        return s
    else:
        sub_channels=[]

RedaAffane

SELECT "user_id", 
       "user_id" || SUM("flag_macro_session_limits") OVER (
            PARTITION BY "user_id" 
            ORDER BY "timestamp" ASC 
           )
           - "flag_macro_session_limits" AS "user_macro_session",
       "channel",
       "timestamp"
FROM(
    SELECT *, 

RedaAffane

SELECT *, 
           CASE WHEN 
               "time_difference" >= INTERVAL '30 days' THEN 1  ELSE 0 END AS "flag_macro_session_limits"
    FROM(
        SELECT *,
               LAG("timestamp",1)  OVER (
                   PARTITION BY "user_id" 
                   ORDER BY "timestamp" DESC
                    )
               - "timestamp" as "time_difference"

RedaAffane

SELECT *, 
       CASE WHEN 
           "timestamp" >= "conversion_timestamp" - interval '30 days' 
           AND "timestamp" <= "conversion_timestamp" 
           THEN 1 else 0 END as "keep_touchpoint"
FROM(
  SELECT "conversions".user_id,
         "conversions".conversion_timestamp,
          "touchpoints".channel,
          "touchpoints".timestamp,

RedaAffane

SELECT "conversions".user_id,
       "conversions".conversion_timestamp,
        "touchpoints".channel,
        "touchpoints".timestamp,
        "conversions".user_id || extract(epoch from "conversions"."conversion_timestamp") AS "macro_user_session"
FROM "conversions" "conversions"
LEFT JOIN "user_touchpoints" "touchpoints"
ON "conversions"."user_id" = "touchpoints"."user_id"

RedaAffane

SELECT "user_id",
       "channel",
       "timestamp"
FROM(       
    SELECT *,
            FIRST_VALUE("touchpoint") over(
                PARTITION BY "user_id", "user_micro_session"
                ORDER BY "timestamp") as "channel"
    FROM(
        SELECT *,

RedaAffane

SELECT *,
       "user_id" || '_' || SUM("new_micro_session")
       OVER (PARTITION BY "user_id"
             ORDER BY "timestamp") AS "user_micro_session"
FROM (
  SELECT
      *,
      CASE WHEN 
          EXTRACT(epoch FROM "timestamp") 
          - LAG(

RedaAffane

SELECT
    *,
    CASE WHEN 
        EXTRACT(epoch FROM "timestamp") 
        - LAG(
            EXTRACT(epoch FROM "timestamp")
            ) OVER (
        PARTITION BY "user_id"
        ORDER BY "timestamp"
                    ) >= 30 * 60 THEN 1 ELSE 0 END as new_micro_session

RedaAffane

SELECT
    *,
    extract(epoch FROM "timestamp") 
    - lag(
        extract(epoch from "timestamp")
        ) over (
        PARTITION BY "user_id"
        order by "timestamp"
                ) as "time_difference"
FROM log_data

RedaAffane

def v_function(A,C_values):
    '''
    This function computes the worth of each coalition.
    inputs:
            - A : a coalition of channels.
            - C_values : A dictionnary containing the number of conversions that each subset of channels has yielded.
    '''
    subsets_of_A = subsets(A.split(","))
    worth_of_A=0
    for subset in subsets_of_A: