From 396acf3bbbe00a192cb0ea0a9ccf91b1d8d2850b Mon Sep 17 00:00:00 2001
From: Fuwn <50817549+Fuwn@users.noreply.github.com>
Date: Sat, 24 Jan 2026 13:09:50 +0000
Subject: Initial commit

Created from https://vercel.com/new
---
 .../data-migrations/populate-revenue-table.sql     | 41 ++++++++++++++++++++++
 1 file changed, 41 insertions(+)
 create mode 100644 db/postgresql/data-migrations/populate-revenue-table.sql

(limited to 'db/postgresql/data-migrations/populate-revenue-table.sql')

diff --git a/db/postgresql/data-migrations/populate-revenue-table.sql b/db/postgresql/data-migrations/populate-revenue-table.sql
new file mode 100644
index 0000000..9df7518
--- /dev/null
+++ b/db/postgresql/data-migrations/populate-revenue-table.sql
@@ -0,0 +1,41 @@
+-----------------------------------------------------
+-- PostgreSQL
+-----------------------------------------------------
+INSERT INTO "revenue"
+SELECT gen_random_uuid() revenue_id,
+    ed.website_id,
+    we.session_id,
+    we.event_id,
+    we.event_name,
+    currency.string_value currency,
+    coalesce(ed.number_value, cast(ed.string_value as numeric(19,4))) revenue,
+    ed.created_at
+FROM event_data ed
+JOIN website_event we 
+ON we.event_id = ed.website_event_id
+JOIN (SELECT website_event_id, string_value
+      FROM event_data
+      WHERE data_key ilike '%currency%') currency
+ON currency.website_event_id = ed.website_event_id
+WHERE ed.data_key ilike '%revenue%';
+
+-----------------------------------------------------
+-- MySQL
+-----------------------------------------------------
+INSERT INTO `revenue`
+SELECT UUID() revenue_id,
+    ed.website_id,
+    we.session_id,
+    we.event_id,
+    we.event_name,
+    currency.string_value currency,
+    coalesce(ed.number_value, cast(ed.string_value as decimal(19,4))) revenue,
+    ed.created_at
+FROM event_data ed
+JOIN website_event we
+ON we.event_id = ed.website_event_id
+JOIN (SELECT website_event_id, string_value
+      FROM event_data
+      WHERE data_key like '%currency%') currency
+ON currency.website_event_id = ed.website_event_id
+WHERE ed.data_key like '%revenue%';
\ No newline at end of file
-- 
cgit v1.2.3

