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;