Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>So using the same approach Ollie jones showed me I figure it out: First I need a list of dates where 'members_count' OR 'network.new' happens</p> <pre><code>SELECT date as current_date FROM activity_log ld WHERE `activity_type` in ('members_count', 'network.new') GROUP BY date ORDER BY date </code></pre> <p>Them I left Join with a list of first date companies appears</p> <pre><code>SELECT MIN(date) AS new_date, company_id FROM activity_log WHERE activity_type = 'network.new' GROUP BY company_id ORDER BY date </code></pre> <p>Also Left Join first time a company count members</p> <pre><code> SELECT min(date) as members_count_date, company_id FROM `activity_networks` WHERE `activity_type` = 'network.daily.members_count' GROUP BY company_id ORDER BY date </code></pre> <p>Then a Make a distinct count of new companies and companies that count members for the first time, group by date. Then I have this:</p> <pre><code> SELECT DATE(FROM_UNIXTIME(ld.date)) as curr_date, COUNT(DISTINCT(new_co)) as new_co, COUNT(DISTINCT(complete_co)) as complete FROM activity_log ld LEFT JOIN (SELECT date AS new_date, company_id as new_co FROM activity_networks WHERE activity_type = 'network.new' GROUP BY company_id ORDER BY date) nd ON (ld.date=nd.new_date) LEFT JOIN (SELECT min(date) as members_count_date, company_id as complete_co FROM `activity_log` WHERE `activity_type` = 'members_count' GROUP BY company_id ORDER BY date) mcd ON (mcd.members_count_date=ld.date) WHERE `activity_type` in ('members_count', 'network.new') GROUP BY DATE(FROM_UNIXTIME(ld.date)) ORDER BY ld.date </code></pre> <p>The distinct function was crucial because the counting wasn't doing right without it. It is not perfect. The column I named <code>'new_co'</code> should bring only incomplete registrations (incomplete means, new register with out members linked to a company), but still the information can be useful.</p>
    singulars
    1. This table or related slice is empty.
    1. POcount first time occurrence of foreign keys
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USPalantir
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POcount first time occurrence of foreign keys
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI'm glad you figured this out.
      singulars
      1. PO
      1. USO. Jones
 

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