How to use an alias in a subquery in SurrealDB?

Question

I was using the starter dataset SurrealDB supplied and working with the daily_sales table. The goal was to query the table and format the result to show the best_sales and best_day grouped by the month_year. While I was able to use the <datetime> casting to format the string into dates and find the max value of the sales to find the best sales in the month, it appeared that finding the day when the best sales happened seemed trickier. I need to equate the result of the grouping sales to the individual days of each sale from the initial daily_sales table. Using a subquery when building the table out in the SELECT clause would not retain the aliases from the main query.

Answer 1

It appears that creating the table within the FROM cause did the trick of being able to use the aliases and also use the $parent variable name.

My final result is this:

select *,
(select value day from only daily_sales where sum_sales = $parent.best_sales) as best_day from (
select time::format(<datetime> day, "%B-%Y") as month_year,
math::max(sum_sales) as best_sales
from daily_sales
group by month_year
)

I used value and only to return a single value without any array. The $parent preset variable helped retain the best_sales variable when compared to the original daily_sales table.

The final result looked like this:

[
  {
    "result": [
      {
        "best_day": "2023-04-11",
        "best_sales": 971548.94,
        "month_year": "April-2023"
      },
      {
        "best_day": "2023-08-04",
        "best_sales": 902987.03,
        "month_year": "August-2023"
      },
      {
        "best_day": "2023-12-27",
        "best_sales": 810232.31,
        "month_year": "December-2023"
      },
      {
        "best_day": "2023-02-02",
        "best_sales": 872801.88,
        "month_year": "February-2023"
      },
      {
        "best_day": "2023-01-10",
        "best_sales": 887891.61,
        "month_year": "January-2023"
      },
      {
        "best_day": "2023-07-15",
        "best_sales": 858560.62,
        "month_year": "July-2023"
      },
      {
        "best_day": "2023-06-04",
        "best_sales": 883341.91,
        "month_year": "June-2023"
      },
      {
        "best_day": "2023-03-09",
        "best_sales": 895781.43,
        "month_year": "March-2023"
      },
      {
        "best_day": "2023-05-07",
        "best_sales": 772227.44,
        "month_year": "May-2023"
      },
      {
        "best_day": "2023-11-06",
        "best_sales": 868215.08,
        "month_year": "November-2023"
      },
      {
        "best_day": "2023-10-06",
        "best_sales": 866574.4,
        "month_year": "October-2023"
      },
      {
        "best_day": "2023-09-13",
        "best_sales": 863761.81,
        "month_year": "September-2023"
      }
    ],
    "status": "OK",
    "time": "34.672708ms"
  }
]

Link to GitHub Discussion (copied from there)

SurrealDB document on params

Github Discussion on using the $parent variable correctly