# Banner stats

**URL:** https://meta.discourse.org/t/banner-stats/274998
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [May 1, 2016, 2:55am UTC](https://meta.discourse.org/t/banner-stats/274998 "2016-05-01T02:55:33Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 1, 2016, 2:55am UTC](https://meta.discourse.org/t/banner-stats/274998/1 "2016-05-01T02:55:33Z")

</div>

Most of mine are “OK, but could be better”

For example, this one works, but I don’t like the duplicate sub-query much

**Banner Stats**

```sql
WITH all_users AS ( SELECT
  COUNT(users.id) AS user_count
  FROM users
  WHERE users.active 
  AND users.suspended_at IS NULL
  AND users.locale IS NOT NULL
)
, read_banner_topic AS ( SELECT 
  COUNT(topic_views.user_id) AS read_count
  FROM topic_views  
  WHERE topic_views.topic_id IN (
    SELECT topics.id 
    FROM topics 
    WHERE topics.archetype IS NOT NULL
    AND topics.archetype LIKE 'banner'
  )
  AND topic_views.user_id IS NOT NULL
)
, dismissed_banner AS ( SELECT 
  COUNT(user_profiles.user_id) AS dismissed_count
  FROM user_profiles
  WHERE user_profiles.dismissed_banner_key IS NOT NULL 
  AND user_profiles.dismissed_banner_key IN (
    SELECT topics.id 
    FROM topics 
    WHERE topics.archetype IS NOT NULL
    AND topics.archetype LIKE 'banner'
  )
  AND user_profiles.user_id IS NOT NULL
)
SELECT all_users.user_count
  , read_banner_topic.read_count
  , dismissed_banner.dismissed_count 
FROM all_users, read_banner_topic, dismissed_banner

```

\* note, read banner topic is _not_ the same as saw banner