Search code snippets, questions, articles...

# Hello

``````-- Return the total spent per each customer ordered highest to lowest spending customers.
SELECT I.CustomerId, Country, Sum(Total) AS Total_Spent
FROM Invoice AS I
JOIN Customer AS C
ON I.CustomerId = C.CustomerId
GROUP BY I.CustomerId
ORDER BY SUM(Total) DESC
;

WITH CTE
AS
(
SELECT
Sales_Id
, SUM(Line_Total) AS total
FROM Sales _Details
GROUP BY Sales_Id
)

SELECT * FROM CTE AS A
INNER JOIN Sales_Details AS B
ON A.Sales_Id = B.Sales_Id

SELECT   * FROM Invoice;

-- Window Functions
-- Take a look at data of interest
SELECT  CustomerId
, InvoiceId
, InvoiceDate
, BillingPostalCode
, Total AS Total_Donation
FROM invoice

-- Give me the total spent per CustomerId
SELECT  CustomerId
, SUM(Total) AS Total_Donation_Per_Person
FROM invoice
GROUP BY CustomerId

-- To get total spent per customer and the associated information, you would need to run queries against the table two times.

-- Window Functions let you run one query. It's much cleaner and more efficient.
-- We haven't had to write a CTE to group the data, and then join back to the table again.
SELECT  CustomerId
, InvoiceId
, InvoiceDate
, BillingPostalCode
, Total AS Total_Donation
, SUM(Total) OVER(PARTITION BY CustomerId) AS donor_total
-- OVER lets us divide the data into windows
-- PARTITION further divides the data down into individual partitions, similar to GROUP BY
-- Each salesId will represent an individual partition
FROM invoice

-- How many donations per each customer?
-- What is the average donation per each individual customer?
-- This can let us segment donors - by some thresholds (\$) and by frequency and timing (date of donations)
SELECT  CustomerId
, InvoiceId
, InvoiceDate
, BillingPostalCode
, Total AS Total_Donation
, COUNT(Total) OVER(PARTITION BY CustomerId) AS number_of_donations
, SUM(Total) OVER(PARTITION BY CustomerId) AS donor_total
, SUM(Total) OVER(PARTITION BY CustomerId) /COUNT(Total) OVER(PARTITION BY CustomerId)  AS average_donation_per_donor
-- OVER lets us divide the data into windows
-- PARTITION further divides the data down into individual partitions, similar to GROUP BY
-- Each salesId will represent an individual partition
FROM invoice

-- What percent of total donations did this donor contribute?
-- What are the daily total donation amounts?
SELECT  CustomerId
, InvoiceId
, InvoiceDate
, BillingPostalCode
, Total AS Total_Donation
, COUNT(Total) OVER(PARTITION BY CustomerId) AS number_of_donations
, SUM(Total) OVER(PARTITION BY CustomerId) AS donor_total
, SUM(Total) OVER() AS overall_total_donations
-- OVER() looks at data as a whole because we haven't partitioned it
FROM invoice

-- Census Data: At SFC we use census data to look at delivery of credits in terms of widening participation.
-- Can help you answer questions like: Given our understanding of past donor behavior and donor demographics,
-- Is there an opportunity to increase donor participation in X region?
SELECT  DISTINCT CustomerId
, BillingPostalCode
, SUM(Total) OVER(PARTITION BY CustomerId) /COUNT(Total) OVER(PARTITION BY CustomerId)  AS average_donation_per_donor
, SUM(Total) OVER()/COUNT(Total) OVER() AS overall_average_donation
, CASE
WHEN SUM(Total) OVER(PARTITION BY CustomerId) /COUNT(Total) OVER(PARTITION BY CustomerId)  >
(SUM(Total) OVER()/COUNT(Total) OVER()) THEN 'Super Donor'
ELSE 'Normal Donor'
END AS donor_class
FROM invoice``````
Best JSON Validator, JSON Tree Viewer, JSON Beautifier at same place. Check how cool is the tool