Useful articles - SQL

Creating a calendar
Use case: creating a calendar for the target completion tracking or activity tracking
Link : https://stackoverflow.com/questions/14113469/generating-time-series-between-two-dates-in-postgresql

Get the labels from a specific tag in a list
Use case: you would like to replace the tags of a single choice attribute in your list by the labels without using a CASE WHEN in order to include any change made.
Output:Mapping table with the tag and the corresponding updated label

WITH lookup AS

(SELECT JSON_ARRAY_ELEMENTS_TEXT(data::JSON -> 'schema')::JSON ->> 'column_tag' AS column_tag,
	   JSON_ARRAY_ELEMENTS_TEXT(JSON_ARRAY_ELEMENTS_TEXT(data::JSON -> 'schema')::JSON -> 'options')::JSON ->> 'key' AS key,
	   JSON_ARRAY_ELEMENTS_TEXT(JSON_ARRAY_ELEMENTS_TEXT(data::JSON -> 'schema')::JSON -> 'options')::JSON ->> 'value' AS VALUE
FROM lists
WHERE id='list_id'),

labels AS (SELECT key,VALUE
FROM lookup
WHERE column_tag = 'attribute_tag')

CODE

You need to replace list_id by the id of the list.

You need to replace attribute_tag by the tag of the single choice attribute