data mining problem for sql

I only can use Compare , Count ,Find ,Join , Save and sort to do this.

question: What would you do to determine how many existing customers purchased another plan / phone?

1. Visitors – anyone who visited the site (anyone on site)
2. Prospects – any consumer who visited the site, but did not log into an account (logging into an account would indicate they were an existing customer)
3. Customers – consumers who have logged into their site account online
4. Hotphone Buyers – consumers who have purchase a wireless phone & plan on site

table looks like this

Date / Label / UserID / Demographic Bucket/ Zip Code/ Time_Stamp

these are the only commands we are allowed to use !! :(

Compare: Comparison of 2 data files. Column 1 indicates the data that matches

Indicate file1, file2 and column 开发者_高级运维to be compared

Count:

Counts rows. Column to be counted must be indicated.

Find:

Allows one to find data that matches criteria. Column to be searched must be indicated.

Join :

Joins 2 files. Files must be sorted by join column first. Resulting file is the join column in column 1, all other columns in file 1 and all other columns in file 2. Indicate file1, file2 and column to be joined.

Save: Allows you to save the results from a command. E.g. if you Find x on column 2, the results will be only that data that qualifies. Use –k1 to save only column 1 , –k2 to save only column 2, etc.

sort:

Sorts data. Column to be sorted must be indicated

---

Compare, Find, Save and Sort are not SQL keywords.

What would you do to determine how many existing customers purchased another plan / phone?

SELECT COUNT(*)
  FROM (SELECT t.userid
          FROM TABLE t
         WHERE t.userid IS NOT NULL
         --AND what determines a phone/plan would go here?
      GROUP BY t.userid
        HAVING COUNT(t.userid) > 1) x

The userid not being null qualifies as a customer, because to be logged in they should have a userid. How to determine who bought a plan? I can't tell from the info, but having more than one instance of the userid and/or plan/phone indicator satisfies the criteria for the inner query. The outer query just counts the [distinct] userids returned.