31 lines
1.3 KiB
SQL
31 lines
1.3 KiB
SQL
-- Replace the unfinished weekly-bucket page-visit tracker with a daily-bucket
|
|
-- design. Daily granularity supports every trending window we expose
|
|
-- (week / month / 3-month / semester / year) with exact day boundaries; the
|
|
-- previous weekly bucket would have produced ±7-day error on any window that
|
|
-- isn't a whole number of weeks (i.e. month/year).
|
|
--
|
|
-- Also drops the never-read `locations.total_visited` integer column. The
|
|
-- lifetime total is recoverable from SUM(visit_count) on this table.
|
|
|
|
DROP INDEX IF EXISTS idx_location_page_visits_week_key_visit_count;
|
|
DROP TABLE IF EXISTS location_page_visits;
|
|
|
|
CREATE TABLE location_page_visits (
|
|
id SERIAL PRIMARY KEY,
|
|
location_id INT NOT NULL REFERENCES locations(id),
|
|
day DATE NOT NULL,
|
|
visit_count BIGINT NOT NULL DEFAULT 0,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
UNIQUE(location_id, day)
|
|
);
|
|
|
|
-- Trending: WHERE day >= now()::date - N ORDER BY visit_count DESC
|
|
CREATE INDEX idx_location_page_visits_day_count
|
|
ON location_page_visits(day, visit_count DESC);
|
|
|
|
-- Per-location lookup (lifetime sum, "visits over time" charts, etc.)
|
|
CREATE INDEX idx_location_page_visits_location_day
|
|
ON location_page_visits(location_id, day);
|
|
|
|
ALTER TABLE locations DROP COLUMN IF EXISTS total_visited;
|