Assumptions:

• The table is named transactions.
• It has the following columns:
  • id (primary key)
  • timestamp (timestamp column)
  • amount (numeric value to aggregate)

PostgreSQL

In PostgreSQL, you can use the SUM() window function with the PARTITION BY clause to calculate a daily total, then divide each row’s amount by that total:

SELECT
    id,
    timestamp::date AS transaction_date,
    amount,
    amount * 100.0 / SUM(amount) OVER (PARTITION BY timestamp::date) AS percentage_of_total
FROM transactions;

BigQuery (Standard SQL)

BigQuery’s window functions work similarly. We use DATE(timestamp) to extract the date:

SELECT
    id,
    DATE(timestamp) AS transaction_date,
    amount,
    amount * 100.0 / SUM(amount) OVER (PARTITION BY DATE(timestamp)) AS percentage_of_total
FROM transactions;

ISO SQL (General Standard)

ISO SQL supports window functions, and assuming a compliant database, the approach is:

SELECT
    id,
    CAST(timestamp AS DATE) AS transaction_date,
    amount,
    amount * 100.0 / SUM(amount) OVER (PARTITION BY CAST(timestamp AS DATE)) AS percentage_of_total
FROM transactions;

Explanation

• SUM(amount) OVER (PARTITION BY <date>) calculates the total amount per day.
• Each row's amount is divided by this total to compute the percentage.
• Multiplying by 100.0 ensures correct decimal handling.