- Diagram database relationships.
- Identify the tables in a database that hold information required to complete complex queries.
- Generate complex ActiveRecord queries using joins, group, order, select, and merge.
- Use the rails dbconsole and rails console to generate ActiveRecord queries.
- What tables would we need to query?
- What information would we need from each table?
- What calculations would we need to perform?
- What SQL would we be able to use to create a table with this information?
We will complete the first endpoint from the challenge:
- Find a quantity of merchants sorted by descending revenue
- This endpoint should return a variable number of merchants ranked by total revenue.
- The URI should follow this pattern:
GET /api/v1/revenue/merchants?quantity=x - where x is the number of merchants to be returned. The quantity parameter is required, and should return an error if it is missing or if it is not an integer greater than 0.
- Example JSON response for
GET /api/v1/revenue/merchants?quantity=2
{
"data": [
{
"id": "1",
"type": "merchant_name_revenue",
"attributes": {
"name": "Turing School",
"revenue": 512.256128
}
},
{
"id": "4",
"type": "merchant_name_revenue",
"attributes": {
"name": "Ring World",
"revenue": 245.130001
}
}
]
}Work through as many of the following endpoints as you can/would like:
HINT: Invoices must have a successful transaction and be shipped to the customer to be considered as revenue.
- Find a quantity of merchants sorted by descending item quantity sold
-
This endpoint should return a variable number of merchants ranked by total number of items sold:
-
The URI should follow this pattern:
GET /api/v1/merchants/most_items?quantity=x -
where
xis the number of merchants to be returned. The quantity should default to 5 if not provided, and return an error if it is not an integer greater than 0. -
Example JSON response for
GET /api/v1/merchants/most_items?quantity=2
{
"data": [
{
"id": "1",
"type": "items_sold",
"attributes": {
"name": "Turing School",
"count": 512
}
},
{
"id": "4",
"type": "items_sold",
"attributes": {
"name": "Ring World",
"count": 128
}
}
]
}- Total revenue generated in the whole system over a start/end date range
-
This endpoint should return the total revenue across all merchants between the given dates, inclusive of the start and end date.
-
The URI should follow this pattern:
GET /api/v1/revenue?start_date=<start_date>&end_date=<end_date> -
Assume your users will only send dates in the format YYYY-MM-DD. Revenue must be counted for any invoices on the end_date as well.
-
An error should be returned if either/both the start date or end date are not provided.
-
Example JSON response for
GET /api/v1/revenue?start=2012-03-09&end=2012-03-24
{
"data": {
"id": null,
"attributes": {
"revenue" : 43201227.8000003
}
}
}- Total revenue for a given merchant
-
This endpoint should return the total revenue for a single merchant.
-
The URI should follow this pattern:
GET /api/v1/revenue/merchants/:id -
Example JSON response for
GET /api/v1/revenue/merchants/1
{
"data": {
"id": "42",
"type": "merchant_revenue",
"attributes": {
"revenue" : 532613.9800000001
}
}
}- Find a quantity of items sorted by descending revenue
-
The endpoint will return a quantity of items ranked by descending revenue.
-
The URI should follow this pattern:
GET /api/v1/revenue/items?quantity=x -
where 'x' is the maximum count of results to return.
- quantity should default to 10 if not provided
- endpoint should return an error if it is not an integer greater than 0.
-
Example JSON response for
GET /api/v1/revenue/items?quantity=1
{
"data": [
{
"id": 4,
"type": "item_revenue",
"attributes": {
"name": "Men's Titanium Ring",
"description": "Fine titanium ring",
"unit_price": 299.99,
"merchant_id": 54,
"revenue": 19823.12985
}
}
]
}- Total revenue of successful invoices which have not yet been shipped
-
Imagine that we want to build a report of the orders which have not yet shipped. How much money is being left on the table for these merchants if they just called Federal Package Logistics to come pick up the boxes...
-
The URI should follow this pattern:
GET /api/v1/revenue/unshipped?quantity=x -
where 'x' is the maximum count of results to return.
- quantity should default to 10 if not provided
- should return an error if it is not an integer greater than 0.
-
Example JSON response for
GET /api/v1/revenue/unshipped?quantity=2
{
"data": [
{
"id": 834,
"type": "unshipped_order",
"attributes": {
"potential_revenue": 5923.78
}
},
{
"id": 28,
"type": "unshipped_order",
"attributes": {
"potential_revenue": 3298.63
}
}
]
}- Revenue report, broken down by month in ascending date order
-
We would like a full report of all revenue, sorted by week (the database can do this for you!). The dates you get back from PostgreSQL will represent the first day of the week
-
The URI should follow this pattern:
GET /api/v1/revenue/weekly -
Example JSON response for
GET /api/v1/revenue/weekly
{
"data": [
{
"id": null,
"type": "weekly_revenue",
"attributes": {
"week": "2012-03-05",
"revenue": 14981117.170000013
}
},
{
"id": null,
"type": "weekly_revenue",
"attributes": {
"week": "2012-03-12",
"revenue": 18778641.380000062
}
},
{
"id": null,
"type": "weekly_revenue",
"attributes": {
"week": "2012-03-19",
"revenue": 19106531.87999994
}
},
{
"id": null,
"type": "weekly_revenue",
"attributes": {
"week": "2012-03-26",
"revenue": 4627284.439999996
}
}
]
}