Sum credit and debit transactions where credits have expiries

Estoy construyendo un sistema de limitación que podemos usar para controlar cuántos correos electrónicos puede enviar un usuario desde nuestra plataforma. Se conceden automáticamente 10.000 emails al mes en su fecha de facturación. Y tienen la opción de comprar más en 5.000 paquetes de correo electrónico. Mi idea para esto era añadir el crédito límite y los débitos a una tabla, y luego utilizar SUM(amount) para calcular cuál es su uso en cualquier momento dado.

Esto es complicado por algunos requisitos:

  1. El usuario recibe automáticamente 10.000 correos electrónicos por mes. Cuando calculamos el límite del usuario tal como está ahora mismo, tiene que mirar desde su última fecha de facturación en adelante.
  2. Los addons que un usuario puede comprar para aumentar su límite tienen una fecha de vencimiento, si no han utilizado la cantidad total acreditada por el addon por vencimiento, pierden la cantidad restante.
  3. Los addons deben persistir para siempre hasta que se utilicen, pero los 10.000 créditos por mes se reinician cada mes.

Actualmente, tengo esta estructura de tabla hecha para una prueba de concepto (la columna de info agregada sólo para explicar lo que está pasando):

enter image description here

Y esta consulta para calcular el límite actual del usuario en el momento de ejecutar la consulta:

SELECT SUM(amount) FROM (
    SELECT SUM(credits.amount) as amount
    FROM limit_transactions credits
    WHERE date >= (
        SELECT date FROM limit_transactions WHERE limit_transactions.billing_reset=1 ORDER BY limit_transactions.date DESC -- to start from where the billing cycle reset
    )
    AND credits.amount > 0 -- to only get credits
    AND (credits.expiry IS NULL OR credits.expiry <= NOW()) -- to exclude expired credits

    UNION ALL

    SELECT SUM(debits.amount) as amount
    FROM limit_transactions debits
    WHERE date >= (
        SELECT date FROM limit_transactions WHERE limit_transactions.billing_reset=1 ORDER BY limit_transactions.date DESC -- to start from where the billing cycle reset
    )
    AND debits.amount < 0 -- to only get debits
) as amount

Creo que esta consulta va en la dirección correcta, pero está dando un resultado incorrecto. Con el conjunto de datos dado vuelve 2000, cuando debe regresar -3000. Esto se debe a que esta parte de la consulta:

AND (credits.expiry IS NULL OR credits.expiry <= NOW()) -- to exclude expired credits

está eliminando completamente los addons que han caducado del cálculo general, en lugar de ser consciente de lo que los débitos han ocurrido mientras que ese addon no fue explorado.

¿Alguien ha implementado una solución como esta antes - o puede sugerir una mejor manera de modelar esta estructura de datos? La otra forma en que podía ir es almacenar créditos en esta tabla, y añadir un used columna que rastrearía cuánto de ese crédito se ha utilizado. Si un usuario envía un correo electrónico, aumentaría el used columna.

Pregunta hecha hace 3 años, 4 meses, 29 días - Por bytebard

2 Respuestas:

  • Para resolver este problema y calcular correctamente el límite actual del usuario teniendo en cuenta los créditos con fecha de vencimiento, se puede ajustar la consulta SQL de la siguiente manera:

    sql
SELECT SUM(sub.amount) as total_amount
FROM (
    SELECT SUM(credits.amount) as amount
    FROM limit_transactions credits
    WHERE credits.date >= (
        SELECT date FROM limit_transactions WHERE billing_reset=1 ORDER BY date DESC LIMIT 1
    )
    AND credits.amount > 0
    AND (credits.expiry IS NULL OR credits.expiry > NOW() OR credits.id IN (
        SELECT DISTINCT credits.id
        FROM limit_transactions credits
        LEFT JOIN limit_transactions debits ON credits.id = debits.id
        WHERE credits.expiry <= NOW()
        AND debits.date >= credits.date
    ))

    UNION ALL

    SELECT SUM(debits.amount) as amount
    FROM limit_transactions debits
    WHERE debits.date >= (
        SELECT date FROM limit_transactions WHERE billing_reset=1 ORDER BY date DESC LIMIT 1
    )
    AND debits.amount < 0
) as sub;

    En esta consulta, se ha ajustado la condición AND (credits.expiry IS NULL OR credits.expiry <= NOW()) para incluir también los créditos con fecha de vencimiento que no han sido utilizados antes de expirar. Se ha añadido una subconsulta para verificar si hay debitos asociados a un crédito vencido que aún no se han restado de la cantidad total.

    Prueba esta consulta en tu base de datos para ver si ahora obtienes el resultado correcto. Si necesitas más ayuda, ¡házmelo saber!

    Respondida el Dec 17, 2020 a las 19:06 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Solución con la tabla existente:

    Para resolver este problema utilizando la estructura de tabla existente, puedes modificar la consulta para excluir solo los créditos caducados que tienen un débito posterior que utiliza parte de su cantidad:

    sql
SELECT SUM(amount) FROM (
    SELECT SUM(credits.amount) AS amount
    FROM limit_transactions credits
    WHERE date >= (
        SELECT date
        FROM limit_transactions
        WHERE limit_transactions.billing_reset = 1
        ORDER BY limit_transactions.date DESC
    )
    AND credits.amount > 0
    AND (
        credits.expiry IS NULL
        OR credits.expiry <= NOW()
        OR EXISTS(
            SELECT 1
            FROM limit_transactions debits
            WHERE debits.date > credits.expiry
                AND debits.amount < 0
                AND credits.transaction_id = debits.transaction_id
        )
    )

    UNION ALL

    SELECT SUM(debits.amount) AS amount
    FROM limit_transactions debits
    WHERE date >= (
        SELECT date
        FROM limit_transactions
        WHERE limit_transactions.billing_reset = 1
        ORDER BY limit_transactions.date DESC
    )
    AND debits.amount < 0
) AS amount

    Esta consulta tiene en cuenta que un crédito caducado puede seguir siendo válido si se ha producido un débito después de su fecha de caducidad. Si ese es el caso, el crédito se incluye en la suma.

    Solución alternativa con una columna "utilizada"

    Si la estructura de la tabla anterior no es adecuada, puedes considerar una solución alternativa que utilice una columna "utilizada":

    Agrega una columna used a la tabla limit_transactions para rastrear cuánto de cada crédito se ha utilizado.

    Realiza un seguimiento de la cantidad utilizada cada vez que se envía un correo electrónico.

    En cualquier momento, para calcular el límite actual, puedes consultar la suma de amount para créditos con amount > used y expiry IS NULL o expiry > NOW().

    Esta solución tiene la ventaja de eliminar cualquier ambigüedad sobre qué créditos están disponibles para su uso. Sin embargo, requiere que mantengas la columna used actualizada con precisión.

    Respondida el Dec 17, 2020 a las 19:09 - por Gemini

    Votos positivos: 0 | Votos negativos: 0