LookML view · dating-platform

The propositions view: date proposals with response counts built in a derived table

At a glance

Does: Builds one row per proposition from propositions, joined to response counts per message type and to the template name. Exposes status, category, city, dates and response measures.Trust: Source-defined
Why: Measures how date proposals perform: how many responses each gets, of which type (intrigue, generic, private date, date response), and by template.Trust: Inferred
Breaks: 3 warnings. Top: response_rate is a dimension built on measures
Grain: One row per proposition, grouped by id in the derived table. id is the primary key.Trust: Source-defined
Source: dating_platform/proposals.view.lookml 168 lines

Source: proposals.view.lookml L1-10

dating_platform/proposals.view.lookmlL1-10 Show context

1- view: propositions2  derived_table:3    sql: |4      WITH proposition_responses AS (5        SELECT  proposition_id,6                type,7                COUNT(1) AS response_count8        FROM    messages M9                JOIN users U10                ON  M.sender_id = U.id11        WHERE   M.state = 'sent'12                AND U.spammer = false13        GROUP BY proposition_id, type

dating-platform, proposals.view.lookml, lines 1-10

Shape

Source-defined

Every field as declared, with the dashboard tiles that read it. Computed from the source; nothing here is edited.

Derived table: persists for 1 hour. Reads warehouse tables directly.

Dimensions 19

Name	Type	SQL	Used by	Tags
`id`L72	int	`${TABLE}.id`	-	primary key
`proposition_template_id`L76	int	`${TABLE}.proposition_template_id`	-	hidden
`category`L80	string	`${TABLE}.category`	-
`city`L82	string	`${TABLE}.city`	-
`created`time, date, week, monthL84	time group	`${TABLE}.created_at`	-
`proposed`date, week, monthL92	time group	`${TABLE}.date`	-
`is_deleted`L99	yesno	`${TABLE}.deleted_at IS NOT NULL`	-	derived
`delivery_status`L102	yesno	`${TABLE}.delivery_status`	-
`lifecycle_minute_of_poster`L105	int	`${TABLE}.lifecycle_minutes`	-
`state`L108	string	`${TABLE}.state`	-
`status`L110	string	`${TABLE}.status`	-
`time`L112	string	`${TABLE}.time`	-
`is_unique_date`L114	yesno	`${TABLE}.unique_date`	-
`user_id`L117	int	`${TABLE}.user_id`	-	hidden
`message_response_type`L121	string	`messages.type`	-	hiddenderived
`template_name`L127	string	`${TABLE}.template_name`	-
`date_response_num`L130	string	`${TABLE}.date_response_num (default)`	-	hiddenimplicit sql
`intrigue_response_num`L132	string	`${TABLE}.intrigue_response_num (default)`	-	hiddenimplicit sql
`response_rate`L146	number	`100.0 * ${response_count} /${count}`	-	derived

Measures 5

Name	Type	SQL	Used by	Tags
`date_response_count`L134	avg	`${TABLE}.date_response_num`	-
`response_count`L138	number	`AVG(CAST(${TABLE}.response_num AS FLOAT))`	-	derived
`intrigue_response_count`L142	sum	`${intrigue_response_num}`	-	derived
`count_intrigue_responses`L150	count_distinct	`messages.id`filters: `message_response_type = Messages::Intrigue`	-	derivedfiltered
`count`L156	count_distinct	`${TABLE}.id`	-

Relationships

Source-defined

Relationships of propositions

This Block
No relationship declared

Text version

propositions is the base view of propositions: base view
propositions is joined into date_ideas: as propositions, on propositions.proposition_templa... (no relationship declared)

Filters and exclusions that change meaning

Responses only count sent messages from non-spammers

Source-defined

The CTE keeps messages with state = 'sent' whose sender has spammer = false. Responses from spammers or unsent drafts never reach any measure.

Source: proposals.view.lookml L4-14

dating_platform/proposals.view.lookmlL4-14 Show context

1- view: propositions2  derived_table:3    sql: |4      WITH proposition_responses AS (5        SELECT  proposition_id,6                type,7                COUNT(1) AS response_count8        FROM    messages M9                JOIN users U10                ON  M.sender_id = U.id11        WHERE   M.state = 'sent'12                AND U.spammer = false13        GROUP BY proposition_id, type14        )15      SELECT  P.id,16              P.user_id,17              P.category,

dating-platform, proposals.view.lookml, lines 4-14

Published by default

Source-defined

In the enterprise model the propositions explore applies status = published unless the query filters on status.

Source: enterprise.model.lookml L39-41

dating_platform/enterprise.model.lookmlL39-41 Show context

36- explore: propositions37  view: propositions38  conditionally_filter:39    propositions.status: published40    unless:41    - propositions.status42  joins:43  - join: users44    sql_foreign_key: propositions.user_id

dating-platform, enterprise.model.lookml, lines 39-41

Worth noticing

Measures named count are averages

Source-defined

response_count is AVG(response_num) typed as number, and date_response_count is type: avg. Both read as counts in the field picker.

Source: proposals.view.lookml L134-141

dating_platform/proposals.view.lookmlL134-141 Show context

131    hidden: true132  - dimension: intrigue_response_num133    hidden: true134  - measure: date_response_count135    type: avg136    decimals: 2137    sql: ${TABLE}.date_response_num138  - measure: response_count139    type: number140    decimals: 2141    sql: AVG(CAST(${TABLE}.response_num AS FLOAT))142  - measure: intrigue_response_count143    type: sum144    sql: ${intrigue_response_num}

dating-platform, proposals.view.lookml, lines 134-141

Template names are computed twice

Source-defined

The derived table builds template_name with COALESCE(PT.name, INITCAP(REPLACE(PT.slug, '-', ' '))), the same rule as proposition_templates.name. A change to one will not reach the other.

Source: proposals.view.lookml L46-51

dating_platform/proposals.view.lookmlL46-51 Show context

43                WHEN PR.type = 'Messages::DateResponse' THEN PR.response_count44                ELSE NULL45              END ) AS date_response_num,46              COALESCE(PT.name, INITCAP(REPLACE(PT.slug, '-', ' '))) AS template_name47      FROM  propositions P48            LEFT JOIN proposition_responses PR49            ON  P.id = PR.proposition_id50            LEFT JOIN proposition_templates PT51            ON  P.proposition_template_id = PT.id52      GROUP BY  P.id,53                P.user_id,54                P.category,

dating-platform, proposals.view.lookml, lines 46-51

What can change the answer 3

Blocker: response_rate is a dimension built on measures

Source-defined

response_rate is declared as a dimension, but its SQL divides the measures response_count and count. Aggregates inside a grouped dimension are not valid SQL.

What goes wrong: Selecting response rate makes the query fail, so the only rate in this view cannot be used.

Source: proposals.view.lookml L146-149

dating_platform/proposals.view.lookmlL146-149 Show context

143    type: sum144    sql: ${intrigue_response_num}145    detail: detail*146  - dimension: response_rate147    type: number148    decimals: 2149    sql: 100.0 * ${response_count} /${count}150  - measure: count_intrigue_responses151    type: count_distinct152    sql: messages.id

dating-platform, proposals.view.lookml, lines 146-149

Caution: Summed responses fan out over the messages join

Inferred

intrigue_response_count sums a per-proposition column. When message_response_type or count_intrigue_responses pulls in the messages join, each proposition repeats once per message.

What goes wrong: Intrigue responses are multiplied by the number of messages on each proposition.

Read the guide: Join fanout

Source: proposals.view.lookml L142-155

dating_platform/proposals.view.lookmlL142-155 Show context

139    type: number140    decimals: 2141    sql: AVG(CAST(${TABLE}.response_num AS FLOAT))142  - measure: intrigue_response_count143    type: sum144    sql: ${intrigue_response_num}145    detail: detail*146  - dimension: response_rate147    type: number148    decimals: 2149    sql: 100.0 * ${response_count} /${count}150  - measure: count_intrigue_responses151    type: count_distinct152    sql: messages.id153    filters:154      message_response_type: Messages::Intrigue155    detail: detail*156  - measure: count157    type: count_distinct158    sql: ${TABLE}.id

dating-platform, proposals.view.lookml, lines 142-155

Note: delivery_status is typed yesno

Inferred

delivery_status is a yes/no field. If the column holds more than two delivery states, everything that is not true reads as No.

Source: proposals.view.lookml L102-104

dating_platform/proposals.view.lookmlL102-104 Show context

99  - dimension: is_deleted100    type: yesno101    sql: ${TABLE}.deleted_at IS NOT NULL102  - dimension: delivery_status103    type: yesno104    sql: ${TABLE}.delivery_status105  - dimension: lifecycle_minute_of_poster106    type: int107    sql: ${TABLE}.lifecycle_minutes

dating-platform, proposals.view.lookml, lines 102-104

Parser checks 1

Source-defined

Mechanical findings from parsing the LookML. They are facts about the files. The cards above explain which ones matter.

Show all 1 check

dimension-uses-measure 1
- proposals.view.lookml:146 Dimension propositions.response_rate references a measure.

At a glance

Shape

Dimensions 19

Measures 5

Relationships

Filters and exclusions that change meaning

Responses only count sent messages from non-spammers

Published by default

Worth noticing

Measures named count are averages

Template names are computed twice

What can change the answer 3

Blocker: response_rate is a dimension built on measures

Caution: Summed responses fan out over the messages join

Note: delivery_status is typed yesno

Parser checks 1

Related

Guides built on this file

Related source files

Source model