Elgg data model can be difficult for newcomers. It is designed to extendable and be used by infinite number of different plugins. You should refer to the elgg site to learn about the data model. Here, I simple provide a list of queries which help you extract useful information from the database. This is the first step in analytics.

Note: database prefix is assumed to be elgg

Listing types

mysql> SELECT DISTINCT
TYPE FROM elggentities;

object
group
user
site

No surprise here. Elgg has 4 types. If you want to insert this code in a plugin,

$query = "SELECT DISTINCT TYPE FROM {$CONFIG->dbprefix}entities";
$rt = get_data($query);
foreach ($rt as $row) {
    print $row->type;
}

Listing types and subtypes

mysql> SELECT DISTINCT e.type, es.subtype, e.subtype AS subtype_id
FROM elggentities e
LEFT JOIN elggentity_subtypes es ON e.subtype = es.id;

type	subtype	subtype_id
site	NULL	0
user	NULL	0
object	plugin	2
object	bookmarks	15
...

List all types and subtypes used by a user
The user's id in this example is 23.

mysql> SELECT DISTINCT e.type, es.subtype, e.subtype AS subtype_id
FROM elggentities e
LEFT JOIN elggentity_subtypes es ON e.subtype = es.id
WHERE owner_guid =23;

type	subtype	subtype_id
group	NULL	0
object	messages	18
object	blog	6
object	groupforumtopic	9
...

Counting entities of a certain type

mysql> SELECT count( * ) AS object_count
FROM elggentities e
WHERE TYPE = 'object'

object_count
57731

Counting entities of a certain type and of a certain user

SELECT count( * ) AS object_count
FROM elggentities e
WHERE TYPE = 'object'
AND owner_guid =23;

object_count
3434

Counting entities by type, subtype, and userid
Note that the subtype id is used.

SELECT count( * ) AS object_count
FROM elggentities e
WHERE TYPE = 'object'
AND subtype =6
AND owner_guid =23

object_count
165

Get account creation time for each user

mysql> SELECT DISTINCT (e.guid), min(e.time_created) as time_created, ue.name
FROM elggentities e, elggusers_entity ue
WHERE e.guid = ue.guid
GROUP BY guid

guid	e.time_created	name
2	1263494014	admin
5	1263494488	Brian
8	1263494524	News
9	1263494524	Darlene
10	1263494524	Evan
11	1263494524	Tim
...