Any ideas how do I prettify this SQL query? This question is abut coding style. I'd like to see how would you improve this code look considering readability as well. Feel free to upvote others solutions you find good.
#1 Initial version:
SELECT greatest(
  0,
  least(
    500,
    (SELECT g.c_daily_limit * (
        SELECT count(*)
        FROM rivals.t_rival_group_auth a
        WHERE a.id_group = g.id) - (
        SELECT count(*)
        FROM rivals.t_order_results rs
        JOIN rivals.t_rivals r ON r.id = rs.id_rival
        WHERE r.id_group = g.id
          AND rs.c_date_order::date = utils.getdate()::date
          AND (rs.c_price IS NOT NULL OR rs.c_failed IS NOT NULL))
      FROM rivals.t_rival_group g
      WHERE g.c_code = _code)
  )
);

#2 An alternative version, should improve readability of nested parameters:
SELECT greatest(
  0,
  least(
    500,
    (
      SELECT g.c_daily_limit * (
        SELECT count(*)
        FROM rivals.t_rival_group_auth a
        WHERE a.id_group = g.id
      ) - (
        SELECT count(*)
        FROM rivals.t_order_results rs
        JOIN rivals.t_rivals r ON r.id = rs.id_rival
        WHERE r.id_group = g.id
          AND rs.c_date_order::date = utils.getDate()::date
          AND (rs.c_price IS NOT NULL OR rs.c_failed IS NOT NULL)
      )
      FROM rivals.t_rival_group g
      WHERE g.c_code = _code
    )
  )
);
The following are a few suggestions on how I'd write the SQL.
Source Control
If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free &amp; private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.
Formatting
I use the following tools for SSMS and Visual Studio, ApexSQL Refactor and Poor Man's T-Sql Formatter. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.
Copy &amp; Paste
If you find yourself copying and pasting the same string or number over and over in your query, then you should define it as a variable. Copy and paste is a design error ~ David Parnas
Commas
I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.
Where Clause
If you put 1=1 at the top of a WHERE condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact. Reference
Common Table Expressions (CTE)
CTE's in your SQL help with documentation. The expression name can then let other developers know why you used that expression e.g. number_of_group_auth or number_of_order_results.
Schema Names
Always reference the schema when selecting an object e.g. [dbo].[Sales].

Also check out the book Clean Code. It will change the way you think about naming conventions.


Revised SQL
Without table definitions and sample records I was unable to test this, but it should give you a good start.
WITH
number_of_group_auth -- table expression names should tell you what they're doing so you don't need to use comments
AS
(
   SELECT 
        [group_count] = COUNT(*)
      , [a].[id_group]
   FROM 
      [rivals].[t_rival_group_auth] AS [a]
)
,
number_of_order_results
AS
(
   SELECT 
        [group_count] = COUNT(*)
      , [r].[id_group]
   FROM
      [rivals].[t_order_results] AS [rs]
      INNER JOIN [rivals].[t_rivals] AS [r] ON [r].[id] = [rs].[id_rival]
   WHERE
      1=1
      AND [rs].[c_date_order]::date = utils.getdate() ::date
      AND
         (
            [rs].[c_price] IS NOT NULL
            OR [rs].[c_failed] IS NOT NULL
         )
)
SELECT
   greatest
   (0, least 
      (500,
         (
            SELECT
                [g].[c_daily_limit] * [number_of_group_auth].[group_count] - [number_of_order_results].[group_count]
            FROM 
                [rivals].[t_rival_group] AS [g]
                LEFT JOIN [number_of_group_auth] AS [a] ON [a].[id_group] = [g].[id]
                LEFT JOIN [number_of_order_results] AS [r] ON [r].[id_group] = [g].[id]
            WHERE 
                [g].[c_code] = [_code]
         )
      )
   );