Skip to content
Window Function (copy)
  • AI Chat
  • Code
  • Report
  • Accompaying Material for Window Function Cheat Sheet

    Table introdution: products

    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT product_id, product_name, model_year, list_price FROM products
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    Table introdution: orders

    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT order_id, order_date FROM sales.orders 
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    Table introdution: order_items

    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT order_id, product_id, discount FROM sales.order_items
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    GROUP BY VS PARTITION BY

    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT
        model_year,
        AVG(list_price) avg_price
    FROM products
    GROUP BY model_year
    
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT
       model_year,
       product_name,
       list_price,
       AVG(list_price) OVER 
         (PARTITION BY model_year) avg_price
    FROM products
    ORDER BY product_name
    
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    ORDER BY

    Unknown integration
    DataFrameavailable as
    df
    variable
    /* Rank price from LOW->HIGH  */
    SELECT 
    product_name, 
    list_price,
    RANK() OVER (ORDER BY list_price ASC) rank
    FROM products
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
       SELECT
        year(order_date) year,
        COUNT(DISTINCT order_id) num_orders
        FROM sales.orders
        GROUP BY year(order_date)
    
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    /* Rank price from HIGH->LOW  */
    SELECT 
    product_name, 
    list_price,
    RANK() OVER (ORDER BY list_price DESC) rank
    FROM products
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    FIRST_VALUE