SQLPad
Sign In

146. Purchase by platform

easy afterpay
  • *************************************************************************;
  • ******************************************: ********************************************
  • *************************************************************

Table: ap_order

Afterpay's order table

  col_name      | col_type
----------------+-------------------
id              | bigint
date            | date
customer_id     | bigint
merchant_id     | bigint
order_channel   | varchar(10)
purchase_amount | float

Sample results 

 2021-08-06 | others   | 1548.3799999999999
 2021-08-07 | desktop  | 2187.4300000000003
 2021-08-07 | mobile   |             1774.8
 2021-08-07 | others   |            1259.52
 2021-08-08 | desktop  |            1488.89

Solution postgres

SELECT
    date,
    CASE WHEN order_channel = 'mobile' THEN 'mobile'
         WHEN order_channel = 'desktop' THEN 'desktop'
         ELSE 'others' END AS platform,
    SUM(purchase_amount)
FROM ap_order
WHERE date >= '2021-08-01'
AND date <= '2021-08-31'
GROUP BY 1,2
ORDER BY 1,2;

Explanation

This query is selecting data from a table called "ap_order". It is selecting the date, the platform, and the sum of the purchase amount. The platform is being determined by a CASE statement that categorizes the order channel as either "mobile", "desktop", or "others".

The query is then filtering the results to only include data from August 2021. The results are then grouped by the date and platform, and ordered by the date and platform.

This query is useful for analyzing sales data by platform and time period. It allows the analyst to see how much revenue is being generated from each platform and how it changes over time.

Expected results


More Afterpay questions

ID Title Level FTPR
142 First order date afterpay easy
40%
143 Customer purchase report afterpay easy
14%
144 Third order afterpay medium
9%
145 Returning customers after first buy afterpay hard
16%