SELECT drink, COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue
  FROM orders
 WHERE created_at > date_trunc('day', now())
 GROUP BY drink
 ORDER BY revenue DESC;

CREATE DATABASE db_coffee;
CREATE TABLE orders (id, customer, drink, total, created_at);

ALTER TABLE drinks
  ADD COLUMN allergens TEXT[] DEFAULT '{}';

SELECT EXTRACT(HOUR FROM created_at) AS hour, COUNT(*) AS orders
  FROM orders
 WHERE created_at > date_trunc('day', now())
 GROUP BY 1
 ORDER BY orders DESC
 LIMIT 1;

INSERT INTO orders (customer, drink, total)
 VALUES ('sarah', 'latte', 4.50)
RETURNING id;

SELECT name, last_order_at
  FROM customers c
 WHERE NOT EXISTS (
   SELECT 1 FROM orders o
    WHERE o.customer = c.name
      AND o.created_at > now() - interval '30 days'
 )
 ORDER BY last_order_at;

CREATE DATABASE db_crm;
CREATE TABLE contacts (...);
CREATE TABLE deals (...);

UPDATE drinks
   SET tags = array_append(tags, 'high-caffeine')
 WHERE caffeine_mg > 100;

SELECT drink, COUNT(*) AS together
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
 WHERE i.item = 'cake'
 GROUP BY drink
 ORDER BY together DESC
 LIMIT 4;

UPDATE orders
   SET status = 'refunded', refunded_at = now()
 WHERE id = (SELECT MAX(id) FROM orders)
RETURNING id, total;

SELECT EXTRACT(HOUR FROM created_at) AS hour,
       ROUND(AVG(total), 2) AS avg_order
  FROM orders
 WHERE created_at > date_trunc('day', now())
 GROUP BY 1
 ORDER BY 1;

ALTER TABLE customers
  ADD COLUMN birthday DATE;

SELECT id, drink, total, created_at
  FROM orders
 WHERE customer IS NULL
    OR customer = ''
 ORDER BY created_at DESC;

UPDATE customers
   SET tier = 'gold', updated_at = now()
 WHERE id IN (
   SELECT customer_id FROM orders
    GROUP BY customer_id
   HAVING COUNT(*) > 100
 );

SELECT drink, COUNT(*) AS orders
  FROM orders
 WHERE created_at > date_trunc('month', now())
 GROUP BY drink
 ORDER BY orders DESC
 LIMIT 5;

SELECT submitted_at, channel, body, tags
  FROM feedback
 WHERE submitted_at > now() - interval '24 hours'
 ORDER BY submitted_at DESC
 LIMIT 5;

SELECT player, score, region
  FROM hackathon_scores
 ORDER BY score DESC
 LIMIT 5;

SELECT thread_id, role, content, created_at
  FROM agent_memory
 WHERE created_at > now() - interval '1 day'
 ORDER BY created_at DESC
 LIMIT 6;

SELECT name, company, last_touch, status
  FROM contacts
 ORDER BY last_touch DESC
 LIMIT 5;

SELECT created_at, customer, drink, total
  FROM orders
 WHERE total > 10
   AND created_at BETWEEN now() - interval '7 days' AND now()
 ORDER BY total DESC;

SELECT category, COUNT(*) AS facts
  FROM agent_memory
 WHERE created_at > now() - interval '7 days'
 GROUP BY category
 ORDER BY facts DESC;

SELECT content, COUNT(*) AS recalls
  FROM agent_recall_log
 GROUP BY content
 ORDER BY recalls DESC
 LIMIT 5;