Marketplace

Category Price min Price max Minimum rating Seller location Sort

1. Most searched terms

Shows successful searches versus no-result searches. This helps identify demand gaps.

SELECT
  lower(trim(sl.query)) AS term,
  COUNT(DISTINCT sl.id) AS searches,
  SUM(CASE WHEN sl.results_count > 0 THEN 1 ELSE 0 END) AS successful_searches,
  SUM(CASE WHEN sl.results_count = 0 THEN 1 ELSE 0 END) AS no_results,
  COUNT(DISTINCT o.id) AS purchases
FROM search_logs sl
LEFT JOIN orders o ON o.search_log_id = sl.id
GROUP BY lower(trim(sl.query))
ORDER BY searches DESC
LIMIT 10;

2. Conversion rate by category

Conversion is calculated from successful searches in a category to orders in that category.

SELECT
  c.name AS category,
  COUNT(DISTINCT sl.id) AS successful_searches,
  COUNT(DISTINCT o.id) AS orders,
  ROUND(
    COUNT(DISTINCT o.id) * 100.0
    / NULLIF(COUNT(DISTINCT sl.id), 0)
  , 2) AS conversion_pct
FROM categories c
LEFT JOIN search_logs sl
  ON sl.dominant_category_id = c.id
 AND sl.results_count > 0
LEFT JOIN orders o
  ON o.search_log_id = sl.id
 AND EXISTS (
    SELECT 1
    FROM products p
    WHERE p.id = o.product_id
      AND p.category_id = c.id
 )
GROUP BY c.name
ORDER BY conversion_pct DESC;

3. Top performing sellers

Top sellers are ranked by revenue and review volume.

SELECT
  s.id,
  s.name AS seller,
  s.location,
  COALESCE(SUM(o.total), 0) AS revenue,
  COUNT(DISTINCT o.id) AS orders,
  COALESCE(AVG(r.rating), s.rating) AS avg_rating,
  COUNT(r.id) AS reviews
FROM sellers s
LEFT JOIN products p ON p.seller_id = s.id
LEFT JOIN orders o ON o.product_id = p.id
LEFT JOIN reviews r ON r.seller_id = s.id
GROUP BY s.id, s.name, s.location, s.rating
ORDER BY revenue DESC;

Top sellers by revenue

Top sellers by reviews

4. Search-to-purchase funnel

Funnel steps help show where users drop off: search, results, clicks, cart, purchase.

WITH funnel AS (
  SELECT 'Searches' AS step, COUNT(*) AS count FROM search_logs

  UNION ALL
  SELECT 'No results', COUNT(*) FROM search_logs WHERE results_count = 0

  UNION ALL
  SELECT 'Results shown', COUNT(*) FROM search_logs WHERE results_count > 0

  UNION ALL
  SELECT 'Product clicked', COUNT(*)
  FROM search_logs sl
  WHERE EXISTS (
    SELECT 1 FROM search_log_results r
    WHERE r.search_log_id = sl.id
  )

  UNION ALL
  SELECT 'Cart event', COUNT(*)
  FROM search_logs sl
  WHERE EXISTS (
    SELECT 1 FROM search_log_events e
    WHERE e.search_log_id = sl.id AND e.event_type = 'cart'
  )

  UNION ALL
  SELECT 'Purchase', COUNT(*)
  FROM search_logs sl
  WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.search_log_id = sl.id
  )
)
SELECT step, count
FROM funnel
ORDER BY step;

5. Average time from search to purchase

Measures how long users wait from the original search event to checkout.

SELECT
  ROUND(
    AVG(EXTRACT(EPOCH FROM o.created_at - sl.searched_at)) / 60,
    2
  ) AS avg_minutes_to_purchase
FROM orders o
JOIN search_logs sl ON sl.id = o.search_log_id;

Order-level detail

Database design

This dashboard assumes normalized tables. The JavaScript simulation uses simplified fields, but the SQL queries reflect proper normalized schema.

CREATE TABLE categories (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE sellers (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    location TEXT NOT NULL,
    rating NUMERIC(3,2) DEFAULT 0.00,
    total_sales INT DEFAULT 0,
    response_rate NUMERIC(4,3),
    joined_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE products (
    id TEXT PRIMARY KEY,
    category_id TEXT NOT NULL REFERENCES categories(id),
    seller_id TEXT NOT NULL REFERENCES sellers(id),
    title TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    condition TEXT NOT NULL DEFAULT 'New',
    stock INT NOT NULL DEFAULT 0,
    shipping TEXT,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE reviews (
    id TEXT PRIMARY KEY,
    product_id TEXT NOT NULL REFERENCES products(id),
    seller_id TEXT NOT NULL REFERENCES sellers(id),
    author TEXT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE search_logs (
    id TEXT PRIMARY KEY,
    user_id TEXT,
    query TEXT NOT NULL,
    searched_at TIMESTAMPTZ NOT NULL,
    results_count INT NOT NULL DEFAULT 0,
    dominant_category_id TEXT REFERENCES categories(id)
);

CREATE TABLE search_log_results (
    search_log_id TEXT NOT NULL REFERENCES search_logs(id),
    product_id TEXT NOT NULL REFERENCES products(id),
    position INT NOT NULL,
    clicked_at TIMESTAMPTZ,
    PRIMARY KEY (search_log_id, product_id)
);

CREATE TABLE search_log_events (
    id TEXT PRIMARY KEY,
    search_log_id TEXT NOT NULL REFERENCES search_logs(id),
    product_id TEXT REFERENCES products(id),
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE orders (
    id TEXT PRIMARY KEY,
    search_log_id TEXT REFERENCES search_logs(id),
    product_id TEXT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_search_logs_query ON search_logs (lower(query));
CREATE INDEX idx_search_logs_searched_at ON search_logs (searched_at);
CREATE INDEX idx_search_logs_results_count ON search_logs (results_count);
CREATE INDEX idx_search_logs_category ON search_logs (dominant_category_id);

CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_products_seller ON products (seller_id);
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_products_title_search ON products USING GIN (to_tsvector('english', title));

CREATE INDEX idx_reviews_product ON reviews (product_id);
CREATE INDEX idx_reviews_seller ON reviews (seller_id);
CREATE INDEX idx_reviews_rating ON reviews (rating);

CREATE INDEX idx_search_log_results_log ON search_log_results (search_log_id);
CREATE INDEX idx_search_log_results_product ON search_log_results (product_id);

CREATE INDEX idx_search_log_events_log ON search_log_events (search_log_id);
CREATE INDEX idx_search_log_events_type ON search_log_events (event_type);

CREATE INDEX idx_orders_search_log ON orders (search_log_id);
CREATE INDEX idx_orders_product ON orders (product_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);