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,
  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,
  accumulated_transactions.*
  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'
WHERE in_transactions.command_type != 'Bán'
ORDER BY in_transactions.id, out_transactions.id
LIMIT 50;