Report not found

There was a problem finding this report.

Report

Query 1 Output - Monthly Avg. Order

Query 2 Output - User LTV

Query 3 Output - Current Status

Hollingsworth - Fishtown - SQL Quiz
June 26, 2019 · Refreshed 4 months ago

Collaborators

Run History
WITH current_status AS ( SELECT order_id, MAX (updated_at) AS max_updated_at FROM fishtownanalytics.order_status_history GROUP BY order_id) SELECT FI.order_id, FI.order_status, FI.updated_at FROM fishtownanalytics.order_status_history AS FI JOIN current_status AS CS ON CS.order_id = FI.order_id AND CS.max_updated_at = FI.updated_at --ORDER BY FI.updated_at DESC LIMIT 100
WITH current_status AS ( SELECT order_id, MAX (updated_at) AS max_updated_at FROM fishtownanalytics.order_status_history GROUP BY order_id) SELECT FI.order_id, FI.order_status, FI.updated_at FROM fishtownanalytics.order_status_history AS FI JOIN current_status AS CS ON CS.order_id = FI.order_id AND CS.max_updated_at = FI.updated_at --ORDER BY FI.updated_at DESC
SELECT user_id, email, SUM (total_price) AS user_ltv FROM fishtownanalytics.orders orders LEFT JOIN fishtownanalytics.users users ON users.id = orders.user_id GROUP BY user_id, users.email ORDER BY user_ltv DESC
SELECT user_id, email, SUM (total_price) AS user_ltv FROM fishtownanalytics.orders orders LEFT JOIN fishtownanalytics.users users ON users.id = orders.user_id GROUP BY user_id, users.email ORDER BY user_ltv DESC
SELECT to_char(created_at, 'yyyy') AS year, to_char(created_at, 'mm') AS month, AVG (total_price) AS avg_order FROM fishtownanalytics.orders GROUP BY year, month ORDER BY month ASC
SELECT to_char(created_at, 'yyyy') AS year, to_char(created_at, 'mm') AS month, AVG (total_price) AS avg_order FROM fishtownanalytics.orders GROUP BY year, month ORDER BY month ASC
<div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div class="mode-grid container"> <div class="row"> <div class="col-md-12"> <mode-text id="text_ad844263-1492-44c1-d5f2-68cf18ad2f62" dataset="dataset" options="text_options"> <h6 class="ql-align-justify">´╗┐Sidney Hollingsworth - hollingsworth.sidney@gmail.com&nbsp;- www.linkedin.com/in/sidneyhollingsworth</h6> <p><br></p> </mode-text> </div> </div> <div class="row"> <div class="col-md-12"> <mode-text id="text_b836b731-e07d-4f99-d758-428d5e0965da" dataset="dataset" options="text_options"> <h4>QUESTION #1</h4> <p><br></p> <p>We're making a push to increase our prices. What is our trend in monthly average order value? Are we making progress?</p> <p><br></p> <p><strong>ANSWER. </strong></p> <p>Yes, in terms of monthly average total price. Average MtM growth in Q2 2017 was 18%.</p> <p>However, of note - MtM% increase from May to June (13%) was almost half that of April to May (24%).</p> <p><br></p> <p><em>Additional insight: </em></p> <p><em>Of the 30 orders in Q2, most orders occurred 4-5pm following by 7-8am, 9-10pm, and 10-11pm in a tie.</em></p> </mode-text> </div> </div> <div class="row" data-row-height="extra-small"> <div class="col-md-4"> <mode-chart id="chart_d0f6979047cd" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-8"></div> </div> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_a657a2c414af" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-table id="table_75b03b624631" dataset="dataset" options="table_options"></mode-table> </div> </div> <div class="row"> <div class="col-md-12"> <mode-text id="text_f6e40c22-d397-4c50-c634-362724a05936" dataset="dataset" options="text_options"> <h4>QUESTION #2</h4> <p><br></p> <p>We're interested in running an email marketing campaign to our top customers. What is the lifetime revenue for each user? Please order the results in order of lifetime revenue, from highest to lowest.</p> <p><br></p> <p><strong>ANSWER.</strong></p> <p><em>See Table below.</em></p> <p><br></p> <p>Further, top four users well above avg. LTV ($342.31):</p> <p><br></p> <ol> <li>Ilyse Binden - ibenden6@pcworld.com - ($633.27)</li> <li>Ericka Kirland - ekirtland0@ning.com - ($522.24)</li> <li>Zulema Mucklestone - zmucklestone4@ezinearticles.com - ($489.78)</li> <li>Joly Courtier - jcourtier7@seesaa.net - ($466.65)</li> </ol> <p><br></p> <p><em>Opportunity for further segmentation exploration: Analyzing user LTV by user account age.</em></p> </mode-text> </div> </div> <div class="row"> <div class="col-md-12"> <mode-table id="table_19e9daf79cdd" dataset="dataset" options="table_options"></mode-table> </div> </div> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_a948fb73ab1c" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-text id="text_3faa4371-99b0-41bf-ee6d-afd0260b656c" dataset="dataset" options="text_options"> <h4>QUESTION #3</h4> <p><br></p> <p>We need to report to operations on current order status. What is the most recent status for each order and when was it set to that value?</p> <p><br></p> <p><strong>ANSWER. </strong></p> <p><em>See Table below. </em></p> <p><br></p> <p><em>*Table provided with pending orders at top to increase visibility as pending orders are more 'actionable'.</em></p> </mode-text> </div> </div> <div class="row"> <div class="col-md-12"> <mode-table id="table_097978ba22c6" dataset="dataset" options="table_options"></mode-table> </div> </div> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_0a08c1ec58b3" dataset="dataset" options="chart_options"></mode-chart> </div> </div> </div>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled