Romain Granger romain9292

## percent_rank.sql
SELECT
  *,
  ROUND(PERCENT_RANK() OVER(ORDER BY revenue)*100,2) AS pct_rank,
FROM
  `datastic.ranking.base_table`;

## quartiles.sql
SELECT
  *,
  NTILE(4) OVER(ORDER BY revenue) AS quartiles,
FROM
  `datastic.ranking.base_table`;

## cume_dist.sql
SELECT
  *,
  ROUND(CUME_DIST() OVER(ORDER BY revenue),2)*100 AS cumulative_distribution,
FROM
  `datastic.ranking.base_table`;

## all_ranking_functions.sql
SELECT
  *,
  ROW_NUMBER() OVER(ORDER BY revenue DESC) AS row_number,
  RANK() OVER(ORDER BY revenue DESC) AS rank,
  DENSE_RANK() OVER(ORDER BY revenue DESC) AS dense_rank,
FROM
  `datastic.ranking.base_table`;

## row_number_partition.sql
SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM
  `datastic.ranking.base_table`;

## row_number_revenue.sql
SELECT
  *,
  ROW_NUMBER() OVER(ORDER BY revenue DESC) AS row_number
FROM
  `datastic.ranking.base_table`;

## row_number_no_order.sql
SELECT
  *,
  ROW_NUMBER() OVER() AS row_number
FROM
  `datastic.ranking.base_table`

## over_time_percentage.sql
WITH
  revenue_product AS (
  SELECT
    product_category,
    DATE(DATE_TRUNC(order_date,MONTH)) AS order_month,
    ROUND(SUM(product_revenue),0) AS revenue_per_category
  FROM
    `datastic.variables.base_table`
  GROUP BY
    1,

## categories_over_time.sql
WITH
  revenue_product AS (
  SELECT
    product_category,
    DATE(DATE_TRUNC(order_date,MONTH)) AS order_month,
    ROUND(SUM(product_revenue),0) AS revenue_per_category
  FROM
    `datastic.variables.base_table`
  GROUP BY
    1,

## share_categories.sql
WITH
  revenue_product AS (
  SELECT
    product_category,
    ROUND(SUM(product_revenue),0) AS revenue_per_category
  FROM
    `datastic.variables.base_table`
  GROUP BY
    1)
  -- Main Query
	SELECT
	*,
	ROUND(PERCENT_RANK() OVER(ORDER BY revenue)*100,2) AS pct_rank,
	FROM
	`datastic.ranking.base_table`;
	SELECT
	*,
	NTILE(4) OVER(ORDER BY revenue) AS quartiles,
	FROM
	`datastic.ranking.base_table`;
	SELECT
	*,
	ROUND(CUME_DIST() OVER(ORDER BY revenue),2)*100 AS cumulative_distribution,
	FROM
	`datastic.ranking.base_table`;
	SELECT
	*,
	ROW_NUMBER() OVER(ORDER BY revenue DESC) AS row_number,
	RANK() OVER(ORDER BY revenue DESC) AS rank,
	DENSE_RANK() OVER(ORDER BY revenue DESC) AS dense_rank,
	FROM
	`datastic.ranking.base_table`;
	SELECT
	*,
	ROW_NUMBER() OVER(PARTITION BY product_category ORDER BY revenue DESC) AS row_number
	FROM
	`datastic.ranking.base_table`;
	SELECT
	*,
	ROW_NUMBER() OVER() AS row_number
	FROM
	`datastic.ranking.base_table`
	WITH
	revenue_product AS (
	SELECT
	product_category,
	DATE(DATE_TRUNC(order_date,MONTH)) AS order_month,
	ROUND(SUM(product_revenue),0) AS revenue_per_category
	FROM
	`datastic.variables.base_table`
	GROUP BY
	1,