0

This is the question: What is the most common stackoverflow tag_type? What companies have a tag of that type?

My Solution:

SELECT type, count(tag) AS count
  FROM tag_type
group by type
order by count desc;

Result of this query is:

type       count
cloud       31
database     6

And so on but I selected cloud in the type column as mentioned below and got my result which is correct.

SELECT tag_company.tag, company.name, tag_type.type
  FROM company
       -- Join to the tag_company table
      INNER JOIN tag_company 
      ON company.id = tag_company.company_id
      -- Join to the tag_type table
      INNER JOIN tag_type
      ON tag_company.tag = tag_type.tag
  -- Filter to most common type
  WHERE type='cloud';

My Question: I have got my desired result though but I want to eliminate the manual work by simply typing (type =cloud) and looking for dynamic way of solving this query. How can I do that?

And how can this query be solved with sub query method? Please help.

Or how could I combine these steps in a single query by using a subquery in the WHERE clause instead of the value 'cloud'?

1
  • Is this a right platform to ask or I should ask this on stack overflow instead? Commented Sep 27, 2020 at 1:44

1 Answer 1

1

I think this question will be fine here, though it could as well have been asked at SO. One nice property of the relational model is that it is closed under relational operations, that is, the result of a query is a new relation. I'll use Common Table Expressions (CTE), but you may as well use a subquery:

WITH type_counts (type, cnt) AS -- type is a bad identifier,
                                -- but I'll ignore that
( SELECT type, count(tag) AS count
  FROM tag_type
  group by type
), company_tag_info AS 
( SELECT tag_company.tag, company.name, tag_type.type
  FROM company
  JOIN tag_company 
      ON company.id = tag_company.company_id
  JOIN tag_type
      ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
    ON cti.type = tc.type

If you just want information related to the top type, you can order by cnt and limit to one:

WITH type_counts (type, cnt) AS -- type is a bad identifier,
                                -- but I'll ignore that
( SELECT type, count(tag) AS count
  FROM tag_type
  group by type
), company_tag_info AS 
( SELECT tag_company.tag, company.name, tag_type.type
  FROM company
  JOIN tag_company 
      ON company.id = tag_company.company_id
  JOIN tag_type
      ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
    ON cti.type = tc.type
ORDER BY tc.cnt DESC
LIMIT 1;

This can be further simplified, but I believe that the above is the most relevant part for your question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.