Incremental Graph per Day

Databases
1

So I don’t have a need for this yet, but I got wondering how I might build it should the need arise (which i have a project coming up that will certainly need it). I was looking at kickstarter.com’s mobile app screenshots today, and saw the common graph of total amount pledged by day. Aug 1st would show 100k, Aug 2 moves up to 101k, etc. So how would I prepare the data for a graph such as this with a data set in this form:


PLEDGE_DATE   |   PLEDGE_AMOUNT
08/01/2012      |    1000
08/01/2012      |    5000
08/02/2012      |    50
08/02/2012      |    500
08/02/2012      |    5848
08/02/2012      |    3838
08/02/2012      |    138

Mini competition:
Who can beat r937 to the punch?

2

not me :slight_smile:

is your question related to how to pull the data out in a form suitable for graphing?

you’d need GROUP BY for that, and a theta self-join, i think, if you’re looking for cumulative total

if just a total pledge by day, not cumulative, it’s a lot simpler

3

So Yes I suppose it would obviously need a sum, grouped by day. But where am I getting the fact that aug 1 + aug 2 pledges equals my total for aug2, aug 3 would be the sum of aug 1-3, etc (assuming the first pledge was aug 1). I feel like I’d have to do a subquery to find the sum for that project where the pledge date <= each date in the range.

With the sample data provided, the graph would show aug 1sts sum at 6,000, and the point for aug 2nd would be at 16374.

4

yeah, you could, but subqueries are occasionally very poor performers

i prefer a theta self-join

first, create this view –

CREATE VIEW pledges_by_date AS
SELECT pledge_date 
     , SUM(pledge_amt) AS pledge_sum
  FROM pledges
GROUP
    BY pledge_date

then, here’s your cumulative query –

SELECT two.pledge_date
     , SUM(one.pledge_sum) AS pledge_cumulative
  FROM pledges_by_date AS two
LEFT OUTER
  JOIN pledges_by_date AS one
    ON one.pledge_date <= two.pledge_date
GROUP
    BY two.pledge_date

simple, yes? :slight_smile:

i’m guessing you’re on sql server, so you could also use WITH syntax instead of the view

5

Ah, very clean and efficient. I’ll be referring back to this in a few months I feel. I currently work with Oracle quite a bit, but I’ll be using this on MySQL.