43.Merchant - 找到最有价值的顾客

营销团队想发优惠券邮件给10位忠实的客户．

基于所花费的总金额列出前十大消费者．

SELECT  customer.firstName, 
        customer.lastName, 
        customer.emailAddress,
        SUM(items.count) purchaseCount, 
        ROUND(SUM(product.unitPrice * items.count))  totalSpent 
FROM purchases UNNEST purchases.lineItems AS items 
JOIN product ON KEYS items.product
JOIN customer ON KEYS purchases.customerId 
GROUP BY customer 
ORDER BY totalSpent DESC LIMIT 10

结果：

{
  "results": [
    {
      "emailAddress": "[email protected]",
      "firstName": "Vernon",
      "lastName": "Satterfield",
      "purchaseCount": 140,
      "totalSpent": 27847
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Nikolas",
      "lastName": "Zulauf",
      "purchaseCount": 185,
      "totalSpent": 27004
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Verlie",
      "lastName": "Fahey",
      "purchaseCount": 112,
      "totalSpent": 26234
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Cary",
      "lastName": "Jerde",
      "purchaseCount": 125,
      "totalSpent": 26035
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Carolanne",
      "lastName": "Hegmann",
      "purchaseCount": 133,
      "totalSpent": 24875
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Coralie",
      "lastName": "O'Kon",
      "purchaseCount": 125,
      "totalSpent": 23348
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Madison",
      "lastName": "Klocko",
      "purchaseCount": 151,
      "totalSpent": 21687
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Luna",
      "lastName": "Rodriguez",
      "purchaseCount": 166,
      "totalSpent": 21564
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Rossie",
      "lastName": "Padberg",
      "purchaseCount": 95,
      "totalSpent": 21318
    },
    {
      "emailAddress": "[email protected]",
      "firstName": "Jeremie",
      "lastName": "Runolfsson",
      "purchaseCount": 182,
      "totalSpent": 21221
    }
  ]
}