Date selector: basket analysis

This final Zeppelin example demonstrates the kind of commercially significant insight we can extract from the retail data. The analysis discovers which other products were purchased in the same baskets as those selected using the product search form over a specific date range using the date selector from the previous example.

  1. Set-up a Zeppelin paragraph with a basket analysis query.

    Paste this code into a new paragraph in your Zeppelin notebook:

    SELECT ps.section_name,
           p.product_name,
           p.prodno,
           COUNT(DISTINCT s2.basketno) num_baskets,
           COUNT(s2.prodno) items_sold,
           SUM(s2.price)/100.00 value
    FROM (SELECT basketno,
                 saledate
          FROM demo_ret.v_ret_sale
          WHERE prodno IN (SELECT prodno
                     FROM demo_ret.v_ret_product
                     WHERE (product_name) ILIKE '%${Manufacturer}%')
          AND saledate between TO_DATE('${fromDate}', 'MM/DD/YYYY') and TO_DATE('${toDate}', 'MM/DD/YYYY')
          GROUP BY 1,2
          ) s1
    JOIN demo_ret.v_ret_sale s2
    ON s1.basketno = s2.basketno
    JOIN demo_ret.v_ret_product p
    ON s2.prodno = p.prodno
    JOIN demo_ret.v_ret_prod_section ps
    ON p.section_no = ps.section_no
    WHERE s2.prodno not in (SELECT prodno
                     FROM demo_ret.v_ret_product
                     WHERE UPPER(product_name) ILIKE '%${Manufacturer}%')
    AND s2.saledate between TO_DATE('${fromDate}', 'MM/DD/YYYY') and TO_DATE('${toDate}', 'MM/DD/YYYY')
    GROUP BY 1,2,3
    ORDER BY 4 DESC;
    

    You define the brand you want to study in the ILIKE clause in the first FROM statement.

  2. Add the paragraph id for this query to the bindings.

    You’ll need to modify the binding in the date selector again, to add the id for this paragraph.

  3. Enter a search term. The output looks like this:

    Sample output
  1. Learn more.

    In this tutorial you’ve seen how Kognitio empowers Zeppelin to allow ad hoc data discovery on large data sets. More resources on Zeppelin dynamic forms can be found at:

Prev: Date selector: basket count by product