How can I make this query more readable?

Question

I have an SQL query that is doing some calculations. Beyond simple formatting changes, I am wondering if it is possible to somehow utilise CTEs to make this query easier to read? I have found a few opportunities where using CTEs can make a big difference to query understandability, but doing it with this query eludes me.

Schema:

CREATE TABLE public.items (
    id bigint NOT NULL,
    uuid uuid NOT NULL,
);

CREATE TABLE public.download_counts (
    item_id uuid NOT NULL,
    date date NOT NULL,
    download_count integer NOT NULL,
);

CREATE TABLE public.view_counts (
    item_id uuid NOT NULL,
    date date NOT NULL,
    view_count integer NOT NULL,
);

Query:

select
  i.uuid as item_id,
  (
    (
      sum(dlw.download_count) * (
        case
        when sum(vlw.view_count) = 0 then sum(dlw.download_count)
        else sum(vlw.view_count)
        end
      )
    ) - (
      sum(dpw.download_count) * (
        case
        when sum(vpw.view_count) = 0 then sum(dpw.download_count)
        else sum(vpw.view_count)
        end
      )
    )
  ) * 100 / (
    sum(dpw.download_count) * (
      sum(dpw.download_count) * (
        case
        when sum(vpw.view_count) = 0 then sum(dpw.download_count)
        else sum(vpw.view_count)
        end
      )
    )
  ) as trending_score
from
  items as i
left join download_counts as dlw
  on dlw.item_id = i.uuid
  and dlw.date between (now()::date - interval '1 week') and (now()::date - interval '1 day')
left join download_counts as dpw
  on dpw.item_id = i.uuid
  and dpw.date between (now()::date - interval '2 week') and (now()::date - interval '8 days')
left join view_counts as vlw
  on vlw.item_id = i.uuid
  and vlw.date between (now()::date - interval '1 week') and (now()::date - interval '1 day')
left join view_counts as vpw
  on vpw.item_id = i.uuid
  and vpw.date between (now()::date - interval '2 week') and (now()::date - interval '8 days')
where dlw.item_id is not null or dpw.item_id is not null or vlw.item_id is not null or vpw.item_id is not null
group by i.uuid;

Can I use CTEs or some other refactoring technique to make this query easier to understand? Thanks!

Laurenz Albe · Answer 1 · 2020-02-26T07:45:50.780

I'd use a subquery to avoid repetition, reduce the number of joins and aggregate selectively using the FILTER clause. The latter is also good for performance.

And I like to capitalize SQL keywords.

SELECT i.uuid as item_id,
       (
          dlw * (CASE WHEN vlw = 0 THEN dlw ELSE vlw END)
        - dpw * (CASE WHEN vpw = 0 THEN dpw ELSE vpw END)
       )
       * 100.0
       / dpw
       / dpw
       / (CASE WHEN vpw = 0 THEN dpw ELSE vpw END)
       as trending_score
FROM (SELECT coalesce(sum(d.download_count), 0)
                FILTER (WHERE d.date BETWEEN current_date - 7
                                         AND current_date - 1)
                AS dlw,
             coalesce(sum(d.download_count), 0)
                FILTER (WHERE d.date BETWEEN current_date - 14
                                         AND current_date - 8)
                AS dpw,
             coalesce(sum(v.view_count), 0)
                FILTER (WHERE v.date BETWEEN current_date - 7
                                         AND current_date - 1)
                AS vlw,
             coalesce(sum(v.view_count), 0)
                FILTER (WHERE v.date BETWEEN current_date - 14
                                         AND current_date - 8)
                AS vpw,
      FROM items as i
         LEFT JOIN download_counts AS d
            ON d.item_id = i.uuid
               AND d.date BETWEEN current_date - 14
                              AND current_date - 1
         LEFT JOIN view_counts AS v
            ON v.item_id = i.uuid
               AND v.date BETWEEN current_date - 14
                              AND current_date - 1
      GROUP BY i.uuid) AS subq;

score 0 · Answer 2 · answered Feb 26 '20 at 07:45

This query could be refactored, but does it become 'easier to read'... :

select item_id
,( sumdlwdc * case when sumvlwvc = 0 then sumdlwdc else 0 end 
 - sumdpwdc * case when sumvpwvc = 0 then sumdpwdc else 0 end )
* 100 / ( sumdpwdc * (sumdpwdc * case when sumvpwvc = 0 then sumdpwdc else 0 end)) as trending_score
from(
    select item_id,
    sum(case when doffset = 0 then download_count else 0 end ) sumdlwdc,
    sum(case when voffset = 0 then view_count else 0 end ) sumvlwvc,
    sum(case when doffset = -1 then download_count else 0 end ) sumdpwdc,
    sum(case when voffset = -1 then view_count else 0 end ) sumvpwvc
    from (
        select
          i.uuid as item_id,
          dlpw.download_count, vlpw.view_count,
          case when dlpw.date between (now()::date - interval '1 week') and (now()::date - interval '1 day') then 0 else -1 end doffset,
          case when vlpw.date between (now()::date - interval '1 week') and (now()::date - interval '1 day') then 0 else -1 end voffset
        from
          items as i
        left join download_counts as dlpw
          on dlpw.item_id = i.uuid
          and dlpw.date between (now()::date - interval '2 week') and (now()::date - interval '1 day')
        left join view_counts as vlpw
          on vlpw.item_id = i.uuid
          and vlpw.date between (now()::date - interval '2 week') and (now()::date - interval '1 day')
        where dlpw.item_id is not null or vlpw.item_id is not null
    )q
    group by item_id
)q1;

How can I make this query more readable?

2 Answers2