Este artigo explora a resolução de problemas comuns de aálise de dados utilizando SQL, focando em padrões de pedidos e login de usuários.

Identificando Usuários com Compras Consecutivas

O objetivo é encontrar usuários que realizaram pedidos por pelo menos 3 dias consecutivos, consultando a tabela order_info. A estratégia envolve o uso da função LEAD para comparar datas de pedidos em sequência dentro de cada grupo de usuário.

Tabela: order_info

Para identificar sequências de 3 dias, partimos do princípio que, se ordenarmos os pedidos de um usuário por data decrescente, a data do pedido 3 posições à frente (usando LEAD(..., 2)) deve ser exatamente 2 dias anterior à data do pedido atual. Isso confirma a sequência.

O seguinte script SQL implementa essa lógica:

SELECT DISTINCT
  sub.user_id
FROM
  (
    SELECT
      user_id,
      LEAD(create_date, 2) OVER (PART BY user_id ORDER BY create_date DESC) AS next_order_date_lag,
      DATE_SUB(create_date, INTERVAL 2 DAY) AS two_days_prior_date
    FROM
      order_info
  ) AS sub
WHERE
  sub.next_order_date_lag = sub.two_days_prior_date;

Proporção de Usuários com Pedido no Dia Seguinte ao Primeiro

Este problema visa calcular a porcentagem de usuários que realizaram um pedido no dia seguinte à sua primeira compra, em relação ao total de usuários únicos na tabela order_info. O resultado deve ser formatado como uma string de percentual com uma casa decimal.

Tabela: order_info

Uma abordagem para solucionar isso é:

1. Denominador: Contar o número total de usuários únicos.
2. Numerador: Identificar usuários que fizeram um pedido no dia seguinte à sua primeira compra. Para isso, podemos ordenar os pedidos de cada usuário por data ascendente e verificar se a diferença entre a data do segundo pedido (obtida com LEAD(..., 1)) e a data do primeiro pedido é exatamente 1 dia, usando DATEDIFF. É crucial remover duplicatas de datas de pedidos por usuário antes dessa análise.

O script SQL a seguir implementa a primiera abordagem:

SELECT
  CONCAT(CAST(cc.numerator AS DECIMAL(10, 1)), '%')
FROM
  (
    SELECT
      COUNT(DISTINCT sub_user.user_id) AS numerator
    FROM
      (
        SELECT
          user_id,
          DATEDIFF(
            LEAD(create_date, 1) OVER (PART BY user_id ORDER BY create_date ASC),
            create_date
          ) AS day_diff
        FROM
          (
            SELECT DISTINCT
              user_id,
              create_date
            FROM
              order_info
          ) AS distinct_orders
      ) AS sub_user
    WHERE
      sub_user.day_diff = 1
  ) AS cc
LEFT JOIN
  (
    SELECT
      COUNT(DISTINCT user_id) AS denominator
    FROM
      order_info
  ) AS dd ON 1 = 1;

Uma segunda abordagem considera o seguinte:

1. Denominador: Contar o número total de usuários únicos.
2. Numerador: Para cada usuário, encontrar a data mínima de pedido (MIN(create_date) OVER (PART BY user_id)). Em seguida, verificar se existe algum pedido cuja data seja exatamente um dia após essa data mínima.

O script SQL para a segunda abordagem:

SELECT
  CONCAT(CAST(bb.numerator AS DECIMAL(10, 1)), '%')
FROM
  (
    SELECT
      COUNT(DISTINCT user_id) AS numerator
    FROM
      (
        SELECT
          user_id,
          create_date,
          MIN(create_date) OVER (PART BY user_id) AS first_order_date
        FROM
          order_info
      ) AS ao
    WHERE
      DATEDIFF(ao.create_date, ao.first_order_date) = 1
  ) AS bb
LEFT JOIN
  (
    SELECT
      COUNT(DISTINCT user_id) AS denominator
    FROM
      order_info
  ) AS dd ON 1 = 1;

Identificando Períodos de Login Consecutivo

Este problema consiste em identificar os intervalos de datas em que os usuários realizaram login por dois ou mais dias consecutivos, utilizando a tabela user_login_detail e a coluna login_ts. A chave para a solução é uma transformação inteligente das datas de login.

Tabela: user_login_detail

A técnica consiste em, após agrupar por usuário e ordenar os logins por data, calcular a diferença entre a data de login e a sua respectiva "rank" (posição sequencial). Essa diferença resulta em um valor constante para cada sequência de login contínuo. Ao agrupar por essa diferença calculada e pelo ID do usuário, podemos determinar o início (MIN(data_login)) e o fim (MAX(data_login)) de cada período de login consecutivo.

O script SQL para encontrar esses períodos é:

SELECT
  user_id,
  MIN(login_date) AS start_date,
  MAX(login_date) AS end_date
FROM
  (
    SELECT
      user_id,
      login_date,
      DATE_SUB(login_date, RANK() OVER (PART BY user_id ORDER BY login_date ASC)) AS login_group_id
    FROM
      (
        SELECT
          user_id,
          DATE(login_ts) AS login_date,
          RANK() OVER (PART BY user_id ORDER BY DATE(login_ts) ASC) AS login_rank
        FROM
          user_login_detail
        GROUP BY
          user_id, DATE(login_ts)
      ) AS ranked_logins
  ) AS grouped_logins
GROUP BY
  user_id, login_group_id
HAVING
  COUNT(login_date) >= 2;