Backend Live Coding

-- ======================================================================
-- Q: What is a CTE? How do you find duplicate records?
--
-- A CTE (WITH) makes complex queries readable. It is ideal for cleaning legacy
-- data before a migration, which I did in practice. After the cleanup I add a
-- unique index on lower(email) so it does not happen again.
-- ======================================================================

WITH dup AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY id) AS rn
  FROM customer
)
SELECT * FROM customer WHERE id IN (SELECT id FROM dup WHERE rn > 1);

-- ======================================================================
-- Q: How do you get the latest price per product (greatest-n-per-group) in
-- PostgreSQL?
-- ======================================================================

SELECT DISTINCT ON (product_id) product_id, amount, created_at
FROM price_history
ORDER BY product_id, created_at DESC;
-- index: (product_id, created_at DESC)

-- ======================================================================
-- Q: Window functions: get the 3 most expensive products per category and the
-- cumulative revenue per day.
--
-- ROW_NUMBER gives a unique number, RANK leaves gaps on ties, DENSE_RANK does
-- not. LAG/LEAD compare with the previous row (e.g. price change). In Django:
-- annotate(rn=Window(RowNumber(), partition_by='category',
-- order_by=F('price').desc())), and since Django 4.2 you can filter directly on
-- a window annotation.
-- ======================================================================

-- top 3 per category
SELECT * FROM (
  SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM product p
) t WHERE rn <= 3;

-- cumulative revenue (running total)
SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

-- ======================================================================
-- Q: INNER JOIN vs LEFT JOIN: list customers and their number of orders,
-- including those with none.
--
-- With INNER JOIN, customers without orders are lost. Use COUNT(o.id) and not
-- COUNT(*), because COUNT(*) also counts the row with NULL as 1.
-- ======================================================================

SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC;

# ======================================================================
# Q: How do you throttle per API key for B2B customers?
#
# The cache backend must be shared (Redis), otherwise each worker counts
# separately. It returns 429 with Retry-After.
# ======================================================================

class B2BKeyThrottle(SimpleRateThrottle):
    scope = 'b2b'

    def get_cache_key(self, request, view):
        key = getattr(request.auth, 'key_id', None)
        return self.cache_format % {'scope': self.scope, 'ident': key or self.get_ident(request)}

REST_FRAMEWORK = {
    'DEFAULT_THROTTLE_CLASSES': ['api.throttles.B2BKeyThrottle'],
    'DEFAULT_THROTTLE_RATES': {'b2b': '600/min'},
}