sql101.sql

SELECT SUM(ssq_quantity) as ssq_quantity_sold
FROM "transaction"
WHERE "command_type" = 'Bán'
LIMIT 50;


SELECT in_transactions.account_number, in_transactions.transaction_date,  in_transactions.command_type, in_transactions.ssq_quantity, out_transactions.transaction_date as selling_date, DATE_PART('day', out_transactions.transaction_date::timestamp - in_transactions.transaction_date::timestamp) as num_of_holding_days
FROM "transaction" as in_transactions
  JOIN "transaction" as out_transactions
    ON out_transactions.command_type = 'Bán' AND
       out_transactions.transaction_date =
       (SELECT MIN (inner_transaction.transaction_date)
        FROM "transaction" as inner_transaction
        WHERE inner_transaction.command_type = 'Bán' AND
              inner_transaction.transaction_date >= in_transactions.transaction_date
       )
WHERE in_transactions.command_type != 'Bán'
ORDER BY in_transactions.transaction_date
LIMIT 50;

SELECT transaction_date as selling_transaction_date
FROM "transaction"
WHERE "command_type" = 'Bán'
ORDER BY transaction_date
LIMIT 50;

SELECT *
FROM "transaction"
WHERE "command_type" = 'Mua' OR "command_type" = 'Switch In'
LIMIT 50;

SELECT in_transactions.id, in_transactions.account_number, in_transactions.transaction_date,  in_transactions.command_type, in_transactions.ssq_quantity, out_transactions.id as selling_trans_id, out_transactions.ssq_quantity as out_ssq_quantity,  in_transactions.ssq_quantity as selling_ssq_quantity, out_transactions.transaction_date as selling_date, DATE_PART('day', out_transactions.transaction_date::timestamp - in_transactions.transaction_date::timestamp) as num_of_holding_days
FROM "transaction" as in_transactions
  JOIN "transaction" as out_transactions
    ON out_transactions.command_type = 'Bán'
WHERE in_transactions.command_type != 'Bán'
ORDER BY in_transactions.id, out_transactions.id
LIMIT 50;

SELECT in_transactions.id,
  in_transactions.account_number,
  in_transactions.transaction_date,
  in_transactions.command_type,
  in_transactions.ssq_quantity,
  SUM(accumulated_transactions.ssq_quantity) as ending,
  out_transactions.id as selling_trans_id,
  out_transactions.ssq_quantity as out_ssq_quantity,

  CASE
    WHEN SUM(accumulated_transactions.ssq_quantity) <= out_transactions.ssq_quantity
     THEN in_transactions.ssq_quantity
    ELSE in_transactions.ssq_quantity -
         (SUM(accumulated_transactions.ssq_quantity) -  out_transactions.ssq_quantity )

  END as selling_ssq_quantity,
  out_transactions.transaction_date as selling_date,
  DATE_PART('day', out_transactions.transaction_date::timestamp - in_transactions.transaction_date::timestamp) as num_of_holding_days

  FROM "transaction" as in_transactions
  JOIN (SELECT
            tempTransaction.id,
            CASE WHEN
                  tempTransaction.command_type = 'Bán'
                    THEN tempTransaction.ssq_quantity*-1
                  ELSE tempTransaction.ssq_quantity
            END as ssq_quantity

        FROM transaction as tempTransaction

    ) as accumulated_transactions
  ON accumulated_transactions.id <= in_transactions.id
  JOIN "transaction" as out_transactions
    ON out_transactions.command_type = 'Bán' AND out_transactions.transaction_date >= in_transactions.transaction_date
GROUP BY in_transactions.id,
  in_transactions.account_number,
  in_transactions.transaction_date,
  in_transactions.command_type,
  in_transactions.ssq_quantity,
  out_transactions.id,
out_transactions.ssq_quantity,
in_transactions.ssq_quantity,
out_transactions.transaction_date,
DATE_PART('day', out_transactions.transaction_date::timestamp - in_transactions.transaction_date::timestamp)
ORDER BY in_transactions.id, out_transactions.id
LIMIT 50;


WITH temporary
   AS
  (SELECT in_transactions.id,
    in_transactions.account_number,
    in_transactions.transaction_date,
    in_transactions.command_type,
    in_transactions.ssq_quantity,
    SUM(accumulated_transactions.ssq_quantity) as ending,
    out_transactions.id as selling_trans_id,
    out_transactions.ssq_quantity as out_ssq_quantity,

    CASE
    WHEN SUM(accumulated_transactions.ssq_quantity) <= out_transactions.ssq_quantity
      THEN in_transactions.ssq_quantity
    ELSE in_transactions.ssq_quantity -
         (SUM(accumulated_transactions.ssq_quantity) -  out_transactions.ssq_quantity

         )
    END as selling_ssq_quantity,
    out_transactions.transaction_date as selling_date,
    DATE_PART('day', out_transactions.transaction_date::timestamp - in_transactions.transaction_date::timestamp) as num_of_holding_days

  FROM "transaction" as in_transactions
    JOIN (SELECT
            tempTransaction.id,
            CASE WHEN
              tempTransaction.command_type = 'Bán'
              THEN tempTransaction.ssq_quantity*-1
            ELSE tempTransaction.ssq_quantity
            END as ssq_quantity

          FROM transaction as tempTransaction

         ) as accumulated_transactions
      ON accumulated_transactions.id <= in_transactions.id
    JOIN "transaction" as out_transactions
      ON out_transactions.command_type = 'Bán' AND out_transactions.transaction_date >= in_transactions.transaction_date
  GROUP BY in_transactions.id,
    in_transactions.account_number,
    in_transactions.transaction_date,
    in_transactions.command_type,
    in_transactions.ssq_quantity,
    out_transactions.id,
    out_transactions.ssq_quantity,
    in_transactions.ssq_quantity,
    out_transactions.transaction_date,
    DATE_PART('day', out_transactions.transaction_date::timestamp - in_transactions.transaction_date::timestamp)
  ORDER BY in_transactions.id, out_transactions.id)


SELECT temporary.*,
    LAG (selling_ssq_quantity, 1, 0.0) OVER (
      PARTITION BY id
      ORDER BY
        selling_date
      ) as previous,
    CASE
    WHEN ending <= out_ssq_quantity
      THEN ssq_quantity - (LAG (selling_ssq_quantity, 1, 0.0) OVER (
        PARTITION BY id
        ORDER BY
          selling_date
        ))
    ELSE ssq_quantity -
         (ending
          -  out_ssq_quantity
          - (LAG (selling_ssq_quantity, 1, 0.0) OVER (
           PARTITION BY id
           ORDER BY
             selling_date
           ))
         )
    END as selling_ssq_quantity_fixed

  FROM temporary;