-- Top 3 products per category by revenue
WITH ranked AS (
  SELECT 
    category,
    product_name,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
  FROM products
)
SELECT * FROM ranked WHERE rank <= 3;

-- Month-over-month revenue growth
SELECT 
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) as growth,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / 
    LAG(revenue) OVER (ORDER BY month), 2
  ) as growth_pct
FROM monthly_revenue;

-- Create table with JSONB column
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_type TEXT,
  payload JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert event with flexible payload
INSERT INTO events (event_type, payload) VALUES 
  ('user_signup', '{"source": "web", "plan": "pro"}'),
  ('purchase', '{"item": "laptop", "price": 999}');

-- Query with JSON operators
SELECT * FROM events 
WHERE payload @> '{"plan": "pro"}';

-- Index for fast JSON queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Create partitioned table
CREATE TABLE sensor_data (
  time TIMESTAMPTZ NOT NULL,
  sensor_id INT,
  value DOUBLE PRECISION
) PARTITION BY RANGE (time);

-- Create partitions
CREATE TABLE sensor_data_2024_01 PARTITION OF sensor_data
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE sensor_data_2024_02 PARTITION OF sensor_data
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Query automatically prunes partitions
EXPLAIN ANALYZE
SELECT * FROM sensor_data 
WHERE time > '2024-01-15' AND time < '2024-01-20';
-- Only scans the January partition