-- PostgreSQL
SELECT 
    DATE_TRUNC('week', order_date) AS week_start,
    COUNT(*) AS orders_count
FROM orders
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;

-- MySQL
SELECT 
    YEARWEEK(order_date, 1) AS year_week,
    COUNT(*) AS orders_count
FROM orders
GROUP BY YEARWEEK(order_date, 1)
ORDER BY year_week;

SELECT 
    customer_id,
    COUNT(order_id) AS orders_count,
    SUM(amount)     AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000
   AND COUNT(order_id) >= 5;

WITH aug AS (
  SELECT message_id, sender_id, receiver_id, sent_date
  FROM   messages
  WHERE  sent_date >= '2022-08-01'
     AND sent_date <  '2022-09-01'
),
conv AS (
  SELECT *,
         CONCAT(
           IIF(sender_id < receiver_id, sender_id, receiver_id), '-',
           IIF(sender_id < receiver_id, receiver_id, sender_id)
         ) AS conv_id
  FROM aug
),
seq AS (
  SELECT *,
         LAG(sender_id) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_sender,
         LAG(sent_date) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_time
  FROM conv
),
fast AS (
  SELECT sender_id
  FROM   seq
  WHERE  prev_sender IS NOT NULL
     AND sender_id <> prev_sender
     AND DATEDIFF(minute, prev_time, sent_date) <= 5
)
SELECT TOP (3)
       sender_id,
       COUNT(*) AS fast_reply_count
FROM   fast
GROUP BY sender_id
ORDER BY COUNT(*) DESC, sender_id;

WITH aug AS (
  SELECT message_id, sender_id, receiver_id, sent_date
  FROM   messages
  WHERE  sent_date >= '2022-08-01'
     AND sent_date <  '2022-09-01'
),
conv AS (
  SELECT *,
         CONCAT(
           IIF(sender_id < receiver_id, sender_id, receiver_id), '-',
           IIF(sender_id < receiver_id, receiver_id, sender_id)
         ) AS conv_id
  FROM aug
),
seq AS (
  SELECT *,
         LAG(sender_id) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_sender,
         LAG(sent_date) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_time
  FROM conv
),
fast AS (
  SELECT sender_id
  FROM   seq
  WHERE  prev_sender IS NOT NULL
     AND sender_id <> prev_sender
     AND DATEDIFF(minute, prev_time, sent_date) <= 5
),
agg AS (
  SELECT sender_id, COUNT(*) AS fast_reply_count
  FROM   fast
  GROUP BY sender_id
),
ranked AS (
  SELECT sender_id, fast_reply_count,
         DENSE_RANK() OVER (ORDER BY fast_reply_count DESC) AS rnk
  FROM   agg
)
SELECT sender_id, fast_reply_count
FROM   ranked
WHERE  rnk <= 3
ORDER BY fast_reply_count DESC, sender_id;

CREATE SCHEMA Orders;
CREATE SCHEMA Shipping;

-- Orders module can only access its schema
CREATE USER OrdersUser WITH DEFAULT_SCHEMA = Orders;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Orders TO OrdersUser;

-- Shipping module can only access its schema
CREATE USER ShippingUser WITH DEFAULT_SCHEMA = Shipping;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Shipping TO ShippingUser;

SELECT DISTINCT ON (user_id) 
       user_id, order_id, created_at
FROM orders
ORDER BY user_id, created_at DESC;

WITH ranked AS (
    SELECT 
        user_id,
        order_id,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT user_id, order_id, created_at
FROM ranked
WHERE rn = 1;