Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm going to stick my neck out and guess what you want. You're asking for "How many company_ids have members each day of activity for the first time". With respect, this is a very hard statement to understand.</p> <p>I think you mean this: for each day, how many company_id values appear for the very first time in a network.new activity type, and how many of those are accompanied by nonzero members.count item in that same day, and how many are not?</p> <p>Here's what you do:</p> <p>First think of a query that will give the very first date for each company appearing in your system. Try this.</p> <pre><code>SELECT MIN(date) networknewdate, company_id FROM table WHERE activity_type = 'network.new' GROUP BY company_id </code></pre> <p>This yields a virtual table of networknewdate, company_id.</p> <p>Next, you need a query that will give the first date a members.count item turns up for each company.</p> <pre><code> SELECT MIN(date) memberscountdate, company_id FROM table WHERE activity_type = 'members.count' GROUP BY date </code></pre> <p>OK, now we have two nice virtual tables each with at most one row for each company_id value. Let's join them, driving the join off the first (network.new) value.</p> <pre><code> SELECT a.networknewdate, a.company_id, IFNULL(b.members_present, 'no') members FROM ( SELECT MIN(date) networknewdate, company_id FROM table WHERE activity_type = 'network.new' GROUP BY company_id ) a LEFT JOIN ( SELECT MIN(date) memberscountdate, company_id, 'yes' members_present FROM table WHERE activity_type = 'members.count' GROUP BY date ) b ON (a.networknewdate = b.memberscountdate and a.company_id = b.company_id) </code></pre> <p>This will return three columns: a date, a company_id, and 'yes' or 'no' saying whether there was a first members.count record on the same day as the first network.new record for each company_id.</p> <p>Now you need to summarize this whole thing so you get one record per day, with the number of 'yes' and the number of 'no' items listed. Here we go.</p> <p>The number of 'yes' records by day.</p> <pre><code>SELECT networknewdate, count(*) yesrecords FROM ( SELECT a.networknewdate, a.company_id, IFNULL(b.members_present, 'no') members FROM ( SELECT MIN(date) networknewdate, company_id FROM table WHERE activity_type = 'network.new' GROUP BY company_id ) a LEFT JOIN ( SELECT MIN(date) memberscountdate, company_id, 'yes' members_present FROM table WHERE activity_type = 'members.count' GROUP BY date ) b ON (a.networknewdate = b.memberscountdate and a.company_id = b.company_id) ) r WHERE r.members = 'yes' GROUP BY networknewdate </code></pre> <p>The number of no records by date is a similar query. Then you need to left join those two queries together on the networknewdate so you get a table of date, yesrecords, norecords. I'm going to leave this as a cut 'n paste exercise for you. It's more than twice as long as the query I wrote ending in <code>GROUP BY networknewdate</code>.</p> <p>Welcome to SQL that implements real world business logic! I think the take-home lesson on this question is that you're asking for a result that's actually quite hard to specify. Once you specify exactly what you want, writing a query to get it is tedious and repetitive but not hard.</p> <p>Another little hint. It may make sense for you to create some views so your queries aren't so enormous. </p>
    singulars
    1. This table or related slice is empty.
    1. POcount first time occurrence of foreign keys
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USO. Jones
    1. USO. Jones
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks, that's helpful but not exactly what I need. I figure it out.
      singulars
      1. PO
      1. USPalantir
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload