**Do you like line charts?** Well, you're going to love this! :-) We have an existing market stats page, with a huge table of numbers, on our site. For every number, you need to place a simple line chart, representing its one-year history, to its immediate right. Repetitive, sure, but that's why there are computers. :-)
We are leaning towards the use of the [Google Chart API][1], but we will entertain a discussion of why we should use the [Google chart Tools][2], (or a third solution), instead. We certainly don't want you spending too much time worrying about how to place which pixel where on a graphics palette!
Owing to the intensity of the necessary calculations, this project breaks down into two parts. Once a day, as the current day's stats are calculated, data for each chart needs to be calculated (probably using stored procedures and/or triggers) and cached (either in the database or on the web server). We're not sure yet whether the data itself, or the actual graphics, should be cached; again, we're open for discussion on this point. The other part of the project is simply displaying the charts on-the-fly, which should of course be trivial.
The charts themselves will each be very simplistic. We have a table that maintains about a year's worth of each of the values on the page, and that's all that should be represented in each line chart. The graphics will have less pixels in width than days in a year, so the presentation will obviously be condensed. e.g., Maybe we use vertical line segments that represent the min/max value over each week, or maybe the API will effectively do that if we just pass all of the daily data, without doing any summarization on our side. You will be expected to balance these types of decisions, showing us what makes sense.
Here's what the market stats page looks like now:
![][3]
We're probably going to add at least two more columns. You really shouldn't care what the numbers are going to be in advance; the charts will just auto-adjust to use the smallest and largest values in the series.
## Deliverables
**Schema:**
// Enumerations
define( "ENUM_PERIOD_TYPE_DAY", "'PERIOD_DAY'" );
define( "ENUM_REGION_AREA", "'REGION_AREA'" );
define( "ENUM_REGION_ZIP", "'REGION_ZIP'" );
CREATE Table hist_listing_stats (
periodType enum(PERIOD_DAY) NOT NULL default PERIOD_DAY
 COMMENT = ’Period covered by this row.’,
IMPORT_FILE_DATE date NOT NULL PRIMARY KEY
 COMMENT = ’Stats as of this data import date.’,
regionType enum(REGION_AREA, REGION_ZIP) NOT NULL
 COMMENT = ’Type of region: area code or zip code.’,
region int unsigned NOT NULL
 COMMENT = ’Area code or zip code.’,
UNIQUE KEY (periodType, IMPORT_FILE_DATE, regionType, region),
AVG_LP_LIST_PRICE double NOT NULL
 COMMENT = ’Average of positive, non-NULL listing prices in US$.’,
NUM_LP_LIST_PRICE int NOT NULL
 COMMENT = ’Number of positive, non-NULL listings.’,
AVG_1BR_LIST_PRICE double NOT NULL
 COMMENT = ’Average of positive, non-NULL 1br listing prices in US$.’,
NUM_1BR_LIST_PRICE int NOT NULL
 COMMENT = ’Number of positive, non-NULL 1br listings.’,
AVG_2BR_LIST_PRICE double NOT NULL
 COMMENT = ’Average of positive, non-NULL 2br listing prices in US$.’,
NUM_2BR_LIST_PRICE int NOT NULL
 COMMENT = ’Number of positive, non-NULL 2br listings.’,
AVG_PRICE_PER_SQFT double NOT NULL
 COMMENT = ’Average price per square foot in US$.’,
NUM_PRICE_PER_SQFT int NOT NULL
 COMMENT = ’Number of price per square foot data points.’,
) ENGINE = InnoDB,
COMMENT = 'Basic statistics on the state of (what was) current listings
at various points in time.’;
You will be expected to deliver on this project using two milestones:
**Development:** Prove you have a stand-alone working solution by presenting a live demonstration and/or some screen-shots, and deliver full source code.
This development can be on your own server if you wish, or we can grant you access to a clean Rackspace Cloud Linux Server at our cost. If you use your own server, be aware that our database (and the corresponding database dump) is currently about five gig, but can be compressed down to 100MB using LRZIP. (You must of course have LRZIP installed.)
**Integration:** You will be granted access to our integration server, where you will prove that your code works well with the rest of the system.
**Environment:** PHP 5.3.2, MySQL 5.1.41, Ubuntu 10.04 LTS.