Your data, queried intelligently. Delivered automatically.

WITH __agg AS (
  SELECT COUNT([t].[transaction_id]) AS [total_txn_count],
    SUM([t].[amount]) AS [total_txn_volume],
    COUNT(CASE WHEN [t].[channel]='ATM' THEN [t].[transaction_id] END) AS [ch_atm],
    COUNT(CASE WHEN [t].[channel]='Online' THEN [t].[transaction_id] END) AS [ch_online],
    COUNT(CASE WHEN [t].[channel]='Mobile' THEN [t].[transaction_id] END) AS [ch_mobile],
    COUNT(CASE WHEN [t].[is_reversed]=1 THEN [t].[transaction_id] END) AS [reversal_count],
    CAST(COUNT(CASE WHEN [t].[is_reversed]=1 THEN [t].[transaction_id] END)
      AS DECIMAL(18,6)) / NULLIF(COUNT([t].[transaction_id]),0) * 100 AS [reversal_rate_pct],
    COUNT(CASE WHEN [tt].[type_name]='Direct Deposit' THEN [t].[transaction_id] END) AS [dd_count],
    SUM(CASE WHEN [tt].[type_name]='Direct Deposit' THEN [t].[amount] END) AS [dd_volume],
    SUM(CASE WHEN [t].[is_pending]=1 THEN [t].[amount] END) AS [pending_value],
      YEAR([t].[transaction_date]) AS [year],
      MONTH([t].[transaction_date]) AS [month],
      DATETRUNC(month, [t].[transaction_date]) AS [txn_month]
  FROM      [dbo].[Transactions] AS [t]
  LEFT JOIN [dbo].[Accounts] AS [a] ON [t].[account_id]=[a].[account_id]
  LEFT JOIN [dbo].[ProductTypes] AS [pt] ON [a].[product_type_id]=[pt].[product_type_id]
  LEFT JOIN [dbo].[TransactionTypes] AS [tt] ON [t].[transaction_type_id]=[tt].[transaction_type_id]
  WHERE [t].[transaction_date] >= '2023-01-01'
    AND [pt].[product_name] IN ('Checking','Savings')
  GROUP BY YEAR([t].[transaction_date]), MONTH([t].[transaction_date]),
      DATETRUNC(month, [t].[transaction_date])
)
SELECT __agg.*,
  LAG([total_txn_count],1) OVER(PARTITION BY [month] ORDER BY [txn_month]) AS [prev_yr_count],
  LAG([dd_volume],1) OVER(ORDER BY [txn_month]) AS [prev_mo_dd_vol]
FROM __agg
ORDER BY [txn_month] ASC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY