SELECT
    product.id,
    product.name,
    product.code,
    pp.price AS regular_price,
    pp.start_date AS price_from,
    pp.expiry_date AS price_to,

    # Products sold quantity (REGULAR SALE)
    (
        SELECT
            SUM( quantity )
        FROM
            sale_postal_code
        WHERE
            product_symbol = product.code
        AND
            sold_at BETWEEN pp.start_date AND IF( pp.expiry_date IS NOT NULL, pp.expiry_date, "2020-01-01" )
        GROUP BY
            product_symbol
    ) AS quantity_form_sale_postal_codes,

    # Products sold quantity (SALE UNDER REGULAR PRICE )
    (
        SELECT
            SUM( quantity )
        FROM
            sale_under_regular_price
        WHERE
            product_symbol = product.code
        AND
            sold_at BETWEEN pp.start_date AND IF( pp.expiry_date IS NOT NULL, pp.expiry_date, "2020-01-01" )
        {distributor_condition}
        {period}
        GROUP BY
            product_symbol
    ) AS quantity_form_sale_under_regular_price,

    # Total sale (UNDER REGULAR PRICE)
    (
        SELECT
            SUM( quantity * sell_price )
        FROM
            sale_under_regular_price
        WHERE
            product_symbol = product.code
        AND
            sold_at BETWEEN pp.start_date AND IF( pp.expiry_date IS NOT NULL, pp.expiry_date, "2020-01-01" )
        {distributor_condition}
        {period}
        GROUP BY
            product_symbol
    ) AS total_sale_form_sale_under_regular_price


FROM
    `product` product
LEFT JOIN
    `product_price` pp ON pp.`product_id` = product.id
LEFT JOIN
    product_category ON product.product_category_id = product_category.id
WHERE
    (
        SELECT
            SUM( quantity )
        FROM
            sale_postal_code
        WHERE
            product_symbol = product.code
        AND
            sold_at BETWEEN pp.start_date AND IF( pp.expiry_date IS NOT NULL, pp.expiry_date, "2020-02-01" )
        {distributor_condition}
        {period}
        GROUP BY
            product_symbol
    ) IS NOT NULL
AND
    (
        SELECT
            SUM( quantity )
        FROM
            sale_under_regular_price
        WHERE
            product_symbol = product.code
        AND
            sold_at BETWEEN pp.start_date AND IF( pp.expiry_date IS NOT NULL, pp.expiry_date, "2020-01-01" )
        {distributor_condition}
        {period}
        GROUP BY
            product_symbol
    ) IS NOT NULL

{and_where}
{ORDER}
{LIMIT}
{offset}