Advance SQL

Window Functions
Recursive Queries
RFM Framework, to see the unified view of customer with his profile and all the products usage

Recursive - to meet the operational needs

Window Functions, : These are powerful tools for performing calculations across set of table -context of the current row, unlike ogher aggregate/groupby windows functions do not cause rows to be grouped into single output row, mean you can compare/compute current row with various other measure required for analytical/operational purposes.

<function_name>(<expression>) OVER ( [PARTITION BY <expression_list>] [ORDER BY <expression_list>] [<frame_clause>])

Various Functions

1. Ranking Functions

ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
RANK(): Assigns a rank to each row within a partition, with gaps in ranking values if there are ties.
DENSE_RANK(): Similar to RANK(), but without gaps in ranking values.
NTILE(n): Divides the rows in each partition into n buckets and assigns a bucket number to each row.

2. Aggregate Functions

SUM(expression): Calculates the sum of the specified expression.
AVG(expression): Calculates the average value of the specified expression.
MIN(expression): Finds the minimum value of the specified expression.
MAX(expression): Finds the maximum value of the specified expression.
COUNT(expression): Counts the number of rows or non-null values of the specified expression.

3. Value Functions

FIRST_VALUE(expression): Returns the first value in the specified expression.
LAST_VALUE(expression): Returns the last value in the specified expression.
NTH_VALUE(expression, n): Returns the n-th value in the specified expression.

4. Navigation Functions

LEAD(expression, offset, default_value): Returns the value of the specified expression from a subsequent row.
LAG(expression, offset, default_value): Returns the value of the specified expression from a preceding row.

5. Statistical Functions

CUME_DIST(): Computes the cumulative distribution of a value in a group of values.
PERCENT_RANK(): Computes the relative rank of a value in a group of values.

6. Row Pattern Recognition Functions

MATCH_NUMBER(): Returns the match number of the row in the pattern.
CLASSIFIER(): Returns the name of the primary pattern variable to which the row is mapped.

7. Percentile Functions

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY expression): Computes the continuous percentile.
PERCENTILE_DISC(percentile) WITHIN GROUP (ORDER BY expression): Computes the discrete percentile.

Practical use cases.

SELECT ID, AMT, Country , booking_date, cume_dist() OVER (ORDER BY "send amount" ASC ) AS CUME_DISTRIBUTION, ROUND(cume_dist() OVER (ORDER BY "AMT" ASC ),2)*100 AS CUME_DISTRIBUTION, rank() over(PARTITION by ID order by "AMT") as rnk_amt, rank() over(PARTITION by nationality order by "AMT") as rnk_nat, row_number() OVER (PARTITION BY nationality ORDER BY booking_date ASC) row_number_nationality , SUM("AMT") OVER (PARTITION BY ID ORDER BY booking_date ASC) rolling_sum FROM fabletbl WHERE ((CAST(transfer_date AS DATE) BETWEEN DATE '2024-05-14' AND DATE '2024-05-14') AND (status = 'complete')) ORDER BY ID ASC, transfer_date ASC

SELECT SalesID,

SaleDate,

Amount,

SUM(Amount) OVER (ORDER BY SaleDate) AS CumulativeSales

FROM Sales;

CREATE OR REPLACE VIEW "RFM_CARDS" ASWITH Cards_recency AS ( SELECT b.cpr , b.crn , min(b.created_on_) creation_date , min(a.trans_date) first_trans_date , DATE_DIFF('day', min(b.created_on_), min(a.trans_date)) days_to_do_first_tran , DATE_DIFF('day', MAX(a.trans_date), current_date) days_since_last_purchase FROM CARDS_TRANS a , profile b WHERE (a."card_ref_number" = b."crn") GROUP BY b.cpr, b.crn), Cards_frequency AS ( SELECT b.cpr , b.crn , count(a."transaction id") transaction_count FROM CARDS_TRANS a , profile b WHERE (a."card_ref_number" = b."crn") GROUP BY b.cpr, b.crn), Cards_monetary AS ( SELECT b.cpr , b.crn , sum(a.spend) total_spent FROM CARDS_TRANS a , profile b WHERE (a."card_ref_number" = b."crn") GROUP BY b.cpr, b.crn), Cards_dmcc AS ( SELECT b.cpr , b.crn , approx_distinct(a."merchant category code") dm FROM CARDS_TRANS a , profile b WHERE (a."card_ref_number" = b."crn") GROUP BY b.cpr, b.crn)SELECT r.crn, r.creation_date, r.first_trans_date, r.days_to_do_first_tran, r.days_since_last_purchase, f.transaction_count, m.total_spent, d.dmFROM (((Cards_recency rINNER JOIN Cards_frequency f ON (r.crn = f.crn))INNER JOIN Cards_monetary m ON (r.crn = m.crn))INNER JOIN Cards_DMCC d ON (r.crn = d.crn))

======================