deeps.md

Problem Statement

Discount model

• Type: [cashback (20 rs for min order of 200), offers (upto 30% off on order of 200rs), vouchers(flat x off until 30 days)]
• Create a Discount class
  • Figure out how to represent in classes
• 2 queries
  • List of all discounts
  • List best discounts given on Rs Y
• Validation of discounts.

Models

from enum import Enum


class DiscountTypes(Enum):
    CASHBACK = "CASHBACK"
    OFFER = "OFFER"
    VOUCHER = "VOUCHER"


class Discount:
    """
    deduction: %age/amount to deduct from original price
    discount_type: enum of cashback, offer, or voucher
    threshold: minimum/maximum value of  original price before or after which the discount activates
    expiry: unix timestamp of expiry -- careful as it doesn't have timezone information
    """

    def __init__(self, deduction: float, discount_type: DiscountTypes, threshold: float,
                 expiry: int, name: str, product_id: int) -> None:
        # self.id will be auto-assigned
        self.deduction = deduction
        self.discount_type = discount_type
        self.threshold = threshold
        self.expiry = expiry
        self.name = name
        self.product_id = product_id # fk

    """
    Indexed columns: id, discount_type, expiry, product_id
    """

    """
    NOTE: As we've decided to generalize all our discount types. Most of the core logic would now
    live either in 1)SQL or 2)App logic.
    Since we're not writing app logic here, the SQLs might seem a bit untidy.

    Apart from this there'd be "users", "products", "products_discounts_mapping" tables.
    """

Relevant SQLs

-- List of all discounts

SELECT id, name FROM discounts

-- List best discounts given on Rs Y
-- A negative discount means cashback (to be handled in app logic), 0 discount means no change in price.
CREATE FUNCTION generate_discounted_price(deduction float, threshold float, discount_type enum, price float) RETURNS float
BEGIN
    AS $$
    SELECT -1 * deduction AS deduction_c WHERE discount_type = 'CASHBACK'
    UNION ALL
    SELECT deduction as deduction_c WHERE discount_type = 'VOUCHER'
    UNION ALL
    SELECT  min(((1 + deduction/100) * price), threshold) AS deduction_c WHERE discount_type = 'OFFER'
    
    $$ language sql;

SELECT name, f.deduction_c, discount_type FROM discounts
LATERAL generate_discounted_price(deduction, threshold, discount_type, ?price_param)
SORT BY f.deduction_c DESC
WHERE id=id_param AND expiry > EXTRACT(EPOCH FROM NOW() AT TIME ZONE 'UTC');

-- List of Valid discounts
SELECT id, name  FROM discounts WHERE expiry > EXTRACT(EPOCH FROM NOW() AT TIME ZONE 'UTC');